Merge table with simple stored procedure for over 1M data

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

Select allOpen in new window

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;

John_Vidmar

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

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.

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.

John_vidmar

please try with your code for the updated file. Hope can understand my question. THX a LOT!
20131210.xlsx

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

Select allOpen in new window

John_vidmar

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

Thanks

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

Select allOpen in new window

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???????

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)

Select allOpen in new window

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