Merge table with simple stored procedure for over 1M data

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
beckyngAsked:
Who is Participating?
 
John_VidmarConnect With a Mentor Commented:
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
 
John_VidmarCommented:
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
 
Daniel WilsonCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
beckyngAuthor Commented:
John_Vidmar

I think that is NOT better solution if Table 1 contain 4 rows via Table 2 contain 3 rows.
0
 
John_VidmarCommented:
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
 
Daniel WilsonCommented:
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
 
beckyngAuthor Commented:
John_vidmar

please try with your code for the updated file. Hope can understand my question. THX a LOT!
20131210.xlsx
0
 
John_VidmarCommented:
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
 
beckyngAuthor Commented:
John_vidmar

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

Thanks
0
 
John_VidmarCommented:
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
 
beckyngAuthor Commented:
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
 
John_VidmarCommented:
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
 
beckyngAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.