Sync tables - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Sync tables

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-04 : 11:08:44

I have an ODBC Source table and a SQL Server Destination Table.
They have exactly the same columns.
If a matching record exists on the primary key, I want to update the target with the source.
If the target has a record (established by primary key comparison) that the source doesnt, I want to delete it from the target.
If the source has a record (established by primary key comparison) that the target doesn't, I want to add it to the destination.

Can this be done with one Insert, one Update & one Delete statement?

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-04 : 11:18:25

I've come up with this for UPDATE and DELETE. Is this the best way?

UPDATE target
SET target.account = source.account
FROM source S
INNER JOIN target T
ON S.PK = T.PK

DELETE FROM Target T WHERE T.PK NOT IN (SELECT PK FROM Source)

I'm stuck on the INSERT though...

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-04 : 11:36:35

Try this for the DELETE and INSERT:

DELETE FROM Target T
WHERE NOT EXISTS (SELECT 'X' FROM Source S
WHERE T.PK = S.PK)

INSERT INTO Target
SELECT * FROM Source S
WHERE NOT EXISTS (SELECT 'X' FROM Target T
WHERE S.PK = T.PK)

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-04 : 11:48:28

Nice one, thanks

Bit stuck on the ODBC source table now. Cant get the syntax/configuration right for an ODBC DSN Linked Server. Can that be done?

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-04 : 11:55:18

Worked it out.
Didnt have the ODBC driver on the server!
Sorry!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources