merging data issue - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

merging data issue

Author

Topic

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2010-11-08 : 08:40:34

Good day, I am having an issue with a database and was hoping someone here might be able to assist. First off let it be known that I am running SQL 2005 Microsoft.

Now I have a query that returns the following rows of data from a much larger table: serial, description, genre, mfg, location, datetime. And this is in a stored proceedure the name of the table this data is in while in the stored proceedure is @cur_prod

This query works fine and does what is required of it.

Now I have this table,named current_machinery, which has the same headers on it. What I want to do is this.

Once the stored proceedure obtains the data and places it in @cur_prod then I want to compare this data to the table current_machine. If a record in @cur_prod has the same serial and location numbers as in current_machinery simply update that record. If there is no matches then insert the new record.

I have read about SQL 2008 having the MERGE command that will do what I want but since we are not running that that command will not work. SO what are my other options for accomplishing this task?

Just so you have an idea of what I am trying to accompish here is how the MERGE command would look if it did function in SQL 2005 or if we had 2008 in house.
=======================================================
MERGE INTO prod_machinery as t1 USING @tablea as t2 on t2.location = t1.location and t2.serial = t1.serial
WHEN MATCHED THEN
update t1.timea = t2.timea
WHEN NOT MATCHED THEN
insert t1.serial,t1.description,t1.genre,t1.mfg,t1.timea,t1.location,t1.code VALUES t2.serial,t2.description,t2.genre,t2.mfg,t2.timea,t2.location,t2.code
========================================================

Thanks again and have a great day.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-08 : 08:52:27

You have to do it in two steps. First update the existing rows and then insert the not existing rows.

update t1
set timea = t2.timea
from prod_machinery t1
join @tablea t2 on t2.location = t1.location and t2.serial = t1.serial

insert prod_machinery(serial,description,genre,mfg,timea,location,code)
select t2.serial,t2.description,t2.genre,t2.mfg,t2.timea,t2.location,t2.code
from @tablea t2
where not exists(select * from prod_machinery x where x.location = t2.location and x.serial = t2.serial)

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources