[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

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
0
beckyng
Asked:
beckyng
  • 6
  • 5
  • 2
1 Solution
 
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
 
beckyngAuthor Commented:
John_Vidmar

I think that is NOT better solution if Table 1 contain 4 rows via Table 2 contain 3 rows.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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
 
John_VidmarCommented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now