Solved

Merge table with simple stored procedure for over 1M data

Posted on 2013-12-10
13
344 Views
Last Modified: 2013-12-13
Dear experts

May I ask if there are simple one stored procedure to generate a consolidate data from  two tables as per attached example file?

I would appreciated it if you can design it whether execute with  over 1 million volume data.

Thanks & Regards, Dear experts
20131210.xlsx
0
Comment
Question by:beckyng
  • 6
  • 5
  • 2
13 Comments
 
LVL 11

Expert Comment

by:John_Vidmar
Comment Utility
Assuming 4-field primary-key (Hierarchy, Product, MatCode, Usage):
create proc whatever
as
select	a.Hierarchy
,	a.Product
,	a.MatCode
,	[Usage_C]	=	a.Usage
,	[Cost_C]	=	a.Cost
,	[Usage_P]	=	b.Usage
,	[Cost_P]	=	b.Cost
from	table1		a
left
join	table2		b	on	a.Hierarchy = b.Hierarchy
				and	a.Product = b.Product
				and	a.MatCode = b.MatCode
				and	a.Usage = b.Usage

Open in new window

0
 
LVL 32

Expert Comment

by:Daniel Wilson
Comment Utility
You just need a LEFT JOIN.

Select T1.Hierarchy, T1.Product, T1.MatCode, T1.Usage as Usage_C, T1.Cost as Cost_C,
  T2.Usage as Usage_P, T2.Cost as Cost_P
From Table1 as T1 Left Join Table2 as T2;
0
 

Author Comment

by:beckyng
Comment Utility
John_Vidmar

I think that is NOT better solution if Table 1 contain 4 rows via Table 2 contain 3 rows.
0
 
LVL 11

Expert Comment

by:John_Vidmar
Comment Utility
table1 left join table2 means an attempt will be made to connect a record from table1 to table2; if the relationship contained in the on-clause does not exist then do not eliminate the record from table1.  Any filtering in the where-clause will eliminate records from table1.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
Comment Utility
I omitted the ON clause ... I got in a hurry.

In fact, John's solution IS correct.  LEFT JOIN ON fields that do not all show up in the right-hand table results in NULL's in fields from the right-hand table ... but all your rows are there.

If you've tried something similar and failed to get all the rows, you probably used an INNER JOIN or merely said JOIN which defaults to an INNER JOIN.
0
 

Author Comment

by:beckyng
Comment Utility
John_vidmar

please try with your code for the updated file. Hope can understand my question. THX a LOT!
20131210.xlsx
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 11

Expert Comment

by:John_Vidmar
Comment Utility
Changed from a left-join to a full-join:
create proc whatever
as
select	[Hierarchy]	=	ISNULL(a.Hierarchy, b.Hierarchy)
,	[Product]	=	ISNULL(a.Product, b.Product)
,	[MatCode]	=	ISNULL(a.MatCode, b.MatCode)
,	[Usage_C]	=	a.Usage
,	[Cost_C]	=	a.Cost
,	[Usage_P]	=	b.Usage
,	[Cost_P]	=	b.Cost
from	table1		a
full
join	table2		b	on	a.Hierarchy = b.Hierarchy
				and	a.Product = b.Product
				and	a.MatCode = b.MatCode
				and	a.Usage = b.Usage

Open in new window

0
 

Author Comment

by:beckyng
Comment Utility
John_vidmar

There would be omitted field value with NULL if use FULL join.

Thanks
0
 
LVL 11

Expert Comment

by:John_Vidmar
Comment Utility
You can wrap all result-fields in an ISNULL-function:
create proc whatever
as
select	[Hierarchy]	=	ISNULL(a.Hierarchy, b.Hierarchy)
,	[Product]	=	ISNULL(a.Product, b.Product)
,	[MatCode]	=	ISNULL(a.MatCode, b.MatCode)
,	[Usage_C]	=	ISNULL(a.Usage,0)
,	[Cost_C]	=	ISNULL(a.Cost,0)
,	[Usage_P]	=	ISNULL(b.Usage,0)
,	[Cost_P]	=	ISNULL(b.Cost,0)
from	table1		a
full
join	table2		b	on	a.Hierarchy = b.Hierarchy
				and	a.Product = b.Product
				and	a.MatCode = b.MatCode
				and	a.Usage = b.Usage

Open in new window

0
 

Author Comment

by:beckyng
Comment Utility
Hi John

thanks for your quick reply!

I would like to merge the two rows into one rows from the result via your solution as follows:
F002.WSU888      F002            WSU888          2      0      0      0
F002.WSU888      F002            WSU888          0      0      1.9      0

Target
F002.WSU888      F002            WSU888          2      0      1.9      0


Any HELPER???????
0
 
LVL 11

Expert Comment

by:John_Vidmar
Comment Utility
Maybe the max-aggregate would help, may take a while with large record-sets:
create proc whatever
as
select	[Hierarchy]	=	ISNULL(a.Hierarchy, b.Hierarchy)
,	[Product]	=	ISNULL(a.Product, b.Product)
,	[MatCode]	=	ISNULL(a.MatCode, b.MatCode)
,	[Usage_C]	=	MAX(a.Usage)
,	[Cost_C]	=	MAX(a.Cost)
,	[Usage_P]	=	MAX(b.Usage)
,	[Cost_P]	=	MAX(b.Cost)
from	table1		a
full
join	table2		b	on	a.Hierarchy = b.Hierarchy
				and	a.Product = b.Product
				and	a.MatCode = b.MatCode
group
by	ISNULL(a.Hierarchy, b.Hierarchy)
,	ISNULL(a.Product, b.Product)
,	ISNULL(a.MatCode, b.MatCode)

Open in new window

0
 

Author Comment

by:beckyng
Comment Utility
hi John

You are very helpful. Thanks a lot.
However, there will be skipped the record of usage_c 1 as follows:
Hierarchy             Product  MatCode      Usage_C      Cost_C      Usage_P      Cost_P
F003.RPL457  F003           RPL457          1      50      1      60
F003.RPL457 F003         RPL457          2      100      2      150


Becky
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 300 total points
Comment Utility
Pulling at straws now... bring back Usage as part of the composite-key?  Difficult to write SQL when primary/foreign keys are unknown:
create proc whatever
as
select	[Hierarchy]	=	ISNULL(a.Hierarchy, b.Hierarchy)
,	[Product]	=	ISNULL(a.Product, b.Product)
,	[MatCode]	=	ISNULL(a.MatCode, b.MatCode)
,	[Usage_C]	=	ISNULL(a.Usage,0)
,	[Cost_C]	=	MAX(a.Cost)
,	[Usage_P]	=	ISNULL(b.Usage,0)
,	[Cost_P]	=	MAX(b.Cost)
from	table1		a
full
join	table2		b	on	a.Hierarchy = b.Hierarchy
				and	a.Product = b.Product
				and	a.MatCode = b.MatCode
				and	a.Usage= b.Usage
group
by	ISNULL(a.Hierarchy, b.Hierarchy)
,	ISNULL(a.Product, b.Product)
,	ISNULL(a.MatCode, b.MatCode)
,	ISNULL(a.Usage,0)
,	ISNULL(b.Usage,0)

Open in new window

0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now