October 23, 2003 at 4:08 pm
Hi,
I want to merge data from one table to another(if key found then update, else insert). These tables reside on different databases on the same SQL Server. Can some suggest the best way of doing this. Using a stored procedure or DTS or any other idea.
Thanks
October 24, 2003 at 6:39 am
In the target database, I would update matches first
UPDATE d
SET d.col1=s.col1,
d.col2=s.col2
...
FROM destinationtable d
INNER JOIN sourcedatabase..sourcetable s
ON s.key = d.key
then insert
INSERT INTO destinationtable
(col1,col2...)
SELECT s.col1,s.col2...
FROM sourcedatabase..sourcetable s
LEFT OUTER JOIN destinationtable d
ON d.key = s.key
WHERE d.key IS NULL
You can put this code in a stored proc if req'd. I do this type of ins/upd daily with my databases using procs.
Far away is close at hand in the images of elsewhere.
Anon.
October 24, 2003 at 8:16 am
more details, please
October 24, 2003 at 9:57 am
Thanks DavidBurrows for your response.
Details:
I have two types of tables some having a serogate key as PK and some having composit PK consisting of 4 columns. They all have FKs going down in a series so the top table has to be processed first. Second database has similar table which are refreshed daily and data from these tables has to be merged (update/insert) to the First database tables on a scheduled basis.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply