SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Help with simple store procedure to add or append
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Patyk
Starting Member

36 Posts

Posted - 02/28/2014 :  13:47:17  Show Profile  Reply with Quote
I have Input Table and Output table. I need to add or update records in my Output table.

Here is my input table. Output table is the same. Item and Code are the primary key.
Item Code Price
01753 r 28.8
01709 us 8
01709 ud 4.8
01709 bu 4.4
01709 r 4.8
01709 b 4.8
01709 p 4.8
01753 cd 123.24
01753 k6 123.24
01753 k5 132.72
01753 k4 143.78
07530 k3 164.32
01753 c 104.28
01753 k2 160.21
01753 k1 164.32
01753 t1 166.37
01753 t2 184.86
01753 t3 205.4
Thanks

sqlsaga
Yak Posting Veteran

USA
93 Posts

Posted - 02/28/2014 :  14:06:15  Show Profile  Reply with Quote
What is the resultset you want to see in the output??

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.
Go to Top of Page

Patyk
Starting Member

36 Posts

Posted - 02/28/2014 :  14:30:44  Show Profile  Reply with Quote
Same result, the input and output tables are the same. Output just has many more records.
Basically I have to compare the fields Item and Code from both tables, if exist update Price otherwise append

thanks
Go to Top of Page

sqlsaga
Yak Posting Veteran

USA
93 Posts

Posted - 02/28/2014 :  15:06:32  Show Profile  Reply with Quote
Hi PatyK, use the below code..


DECLARE @Source TABLE
(
Item VARCHAR(10),
Code VARCHAR(10),
Price DECIMAL(10,2)
)

DECLARE @Target TABLE
(
Item VARCHAR(10),
Code VARCHAR(10),
Price DECIMAL(10,2)
)

INSERT INTO @Source VALUES('01753', 'r', 28.8), ('01709', 'us', 8), ('01709', 'ud', '4.8'), ('01709', 'bu', 4.4), ('01709', 'r', 4.8)
,('01709', 'b', 4.8), ('01709', 'p', 4.8), ('01753', 'cd', 123.24), ('01753', 'k6', 123.24), ('01753', 'k5', 132.72)
,('01753', 'k4', 143.78), ('07530', 'k3', 164.32), ('01753', 'c', 104.28), ('01753', 'k2', 160.21), ('01753', 'k1', 164.32)
,('01753', 't1', 166.37), ('01753', 't2', 184.86), ('01753', 't3', 205.4)


MERGE @Target AS TARGET
USING (SELECT Item, Code, Price From @Source) AS SOURCE
ON (Source.Item = Target.Item AND Source.Code = Target.Code)
WHEN MATCHED THEN
UPDATE 
SET TARGET.Price = Source.Price
WHEN NOT MATCHED THEN
INSERT(Item, Code, Price) VALUES (SOURCE.Item, SOURCE.Code, SOURCE.Price);

SELECT * FROM @Source
SELECT * FROM @Target


Since you provided data, that was never re occuring, you don't see any updates.. You can test using some dummy data as well...



Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.
Go to Top of Page

Patyk
Starting Member

36 Posts

Posted - 02/28/2014 :  15:28:13  Show Profile  Reply with Quote
Ok thanks the only think is that I wish there would be no hard codding since i want to run this a a store procedure possibly once per week with different Input data.

Regards,
Go to Top of Page

sqlsaga
Yak Posting Veteran

USA
93 Posts

Posted - 02/28/2014 :  16:41:14  Show Profile  Reply with Quote
Where did you find hard coding? Sorry I didn't get you...

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.
Go to Top of Page

Patyk
Starting Member

36 Posts

Posted - 02/28/2014 :  17:14:33  Show Profile  Reply with Quote
Just under insert into you have this code. I mean that I can't rewrite the store procedure to enter new values every week.

INSERT INTO @Source VALUES('01753', 'r', 28.8), ('01709', 'us', 8), ('01709', 'ud', '4.8'), ('01709', 'bu', 4.4), ('01709', 'r', 4.8)
,('01709', 'b', 4.8), ('01709', 'p', 4.8), ('01753', 'cd', 123.24), ('01753', 'k6', 123.24), ('01753', 'k5', 132.72)
,('01753', 'k4', 143.78), ('07530', 'k3', 164.32), ('01753', 'c', 104.28), ('01753', 'k2', 160.21), ('01753', 'k1', 164.32)
,('01753', 't1', 166.37), ('01753', 't2', 184.86), ('01753', 't3', 205.4)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000