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

General SQL Server Forums

New to SQL Server Programming

Sync tables

Author

Topic

rockingdesi
Starting Member

31 Posts

Posted - 2007-09-24 : 15:42:14

Hi all,

I have three tables and i want to bring all tables into sync.
My problem is that the difference in the amount of records is huge.

CORE -- 85659 This table doesnt have a PK
CRF -- 59895 This table has a composite key (MIN,MAX)
DS -- 58595 This table also has a the same composite key (MIN,MAX)

All the tables have their own UIDs. Additionally CRF has a column which stores the UID of CORE table and DS has a column which stores UID of the CRF table.

CORE --> CRF --> DS
UID --> UID,CORE_UID --> UID,CRF_UID

I have only 3 columns (UID, MIN, MAX) that can be used to sync all the tables. Can anybody help me...?

Thanks in advance....

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-24 : 16:01:59

What do you mean by sync? If your records in CORE are to be taken as source and applied to CRF and DS tables, you can do a batch UPDATE followed by a batch INSERT to insert missing records.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rockingdesi
Starting Member

31 Posts

Posted - 2007-09-24 : 16:06:46

Well i want to take the DS table as the base and then eliminate entries from the CRF and CORE tables. If you suggest to do a batch update, can you let me know what is the process i have to follow or can you help me write the script? I would rather prefer the MIN and MAX columns to sync all the data i have.

quote:
Originally posted by dinakar

What do you mean by sync? If your records in CORE are to be taken as source and applied to CRF and DS tables, you can do a batch UPDATE followed by a batch INSERT to insert missing records.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-24 : 16:21:20

[code]
UPDATE C
SET C.col1 = D.col1
, C.col2 = D.col2
.....
FROM CRF c
JOIN DS D ON C.MIN = D.MIN AND C.MAX = D.MAX

UPDATE C
c.col1 = D.col1
, C.col2 = D.col2
.....
FROM CORE C
JOIN DS D ON C.UID = D.UID

-- Delete records not present in DS table
DELETE C
FROM CORE C
WHERE NOT EXISTS ( SELECT * FROM DS D WHERE D.UID = C.UID)
[/code]

You might want to a SELECT and verify the rows before running the DELETE.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources