?
Solved

Merge table with simple stored procedure for over 1M data

Posted on 2013-12-10
13
Medium Priority
?
359 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39708580
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
ID: 39708585
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
ID: 39708593
John_Vidmar

I think that is NOT better solution if Table 1 contain 4 rows via Table 2 contain 3 rows.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39708614
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
ID: 39708615
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
ID: 39708636
John_vidmar

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

Expert Comment

by:John_Vidmar
ID: 39708661
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
ID: 39708748
John_vidmar

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

Thanks
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39708769
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
ID: 39708821
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
ID: 39709504
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
ID: 39710245
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 900 total points
ID: 39711225
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

762 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