Help with simple store procedure to add or append

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with simple store procedure to add or append

Author

Topic

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-02-28 : 13:47:17

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

93 Posts

Posted - 2014-02-28 : 14:06:15

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.

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-02-28 : 14:30:44

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

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-02-28 : 15:06:32

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.

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-02-28 : 15:28:13

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,

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-02-28 : 16:41:14

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.

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-02-28 : 17:14:33

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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources