 | |
Auto update insert delete bet. 2 tables
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Auto update insert delete bet. 2 tables -
07-14-2003
, 02:30 PM
#2
| |||
| |||
|
|
I am trying to develop an automatic synchronization between 2 tables and run it once a day. The fields of the Source Table are : SSN, first, last, phone, The fields of the Destination Table are : IT_ID, SSN, first, last, phone, email (the same 5 fields + a unique ID) During this synchronization, I would like to: a.. delete records in my destination table if they have been deleted in my source table b.. insert records in my destination table if they have been inserted in my source table c.. update records in my destination table if records are in the source table I heard that the best way to do it is using Data Driven Task, but I don't know how to use them for my matter... Any help is welcome, Thank you, Flo |
#3
| |||
| |||
|
|
OK What you would do is this Have a trigger on the Source. it logs to a table the INSERT/UPDATE/DELETE. You would generally have a field in there as well as a flag with values of U,I,D for obvious reasons. You would then use this to determine through the Data Driven Query Task what to do with the data. If there are not too many records in the source it may be quicker to DTS over the whole lot Then use TSQL to do it Key in Source not in Dest = INSERT Key in DEST not in Source = DELETE Key in both = UPDATE -- ---------------------------- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Flo" <Florent.Boulanger (AT) WJMC (DOT) org> wrote in message news:#PuQz5jSDHA.1556 (AT) TK2MSFTNGP10 (DOT) phx.gbl... I am trying to develop an automatic synchronization between 2 tables and run it once a day. The fields of the Source Table are : SSN, first, last, phone, The fields of the Destination Table are : IT_ID, SSN, first, last, phone, email (the same 5 fields + a unique ID) During this synchronization, I would like to: a.. delete records in my destination table if they have been deleted in my source table b.. insert records in my destination table if they have been inserted in my source table c.. update records in my destination table if records are in the source table I heard that the best way to do it is using Data Driven Task, but I don't know how to use them for my matter... Any help is welcome, Thank you, Flo |
#4
| |||
| |||
|
|
If you are DTSing over the whole table then you simply pump the whole table across. You then drop onto the designer surface 3 ExecuteSQL tasks 1. INSERTS INSERT INTO DESTTABLE(col list) SELECT col list FROM SOURCETABLE S LEFT OUTER JOIN DESTTABLE D ON S.Key = D.Key WHERE D.Key IS NULL 2. DELETES DELETE D FROM DESTTABLE D LEFT OUTER JOIN SOURCETABLE S ON D.Key = S.Key WHERE S.Key IS NULL 3. UPDATES UPDATE D SET........................... FROM DESTTABLE D JOIN SOURCETABLE S ON D.Key = S.Key -- ---------------------------- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Flo" <Florent.Boulanger (AT) WJMC (DOT) org> wrote in message news:OaQf$5lSDHA.2148 (AT) TK2MSFTNGP11 (DOT) phx.gbl... I forgot to mention that I have about 2000 records so I think I could DTS over the whole lot and not having a trigger on the source. My problem is that I don't know how? I am using DTS designer and I don't know if I have to use a special query on the source? I didn't specify any. I specified the following transformation: Function Main() Select Case Trim(DTSSource("employee")) Case "New" Main = DTSTransformStat_InsertQuery Case "Change" Main = DTSTransformStat_UpdateQuery Case "Delete" Main = DTSTransformStat_DeleteQuery Case Else Main = DTSTransformStat_SkipRow End Select End Function And then for the insert I specified something like that: INSERT INTO dest_table (SSN, FIRST_NAME, LAST_NAME, WORK_PHONE) VALUES (?, ?, ?, ?) But the parameters are values of the destination table instead of of the source table. Thank you, Flo "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message news:OdqtyclSDHA.1748 (AT) TK2MSFTNGP11 (DOT) phx.gbl... OK What you would do is this Have a trigger on the Source. it logs to a table the INSERT/UPDATE/DELETE. You would generally have a field in there as well as a flag with values of U,I,D for obvious reasons. You would then use this to determine through the Data Driven Query Task what to do with the data. If there are not too many records in the source it may be quicker to DTS over the whole lot Then use TSQL to do it Key in Source not in Dest = INSERT Key in DEST not in Source = DELETE Key in both = UPDATE -- ---------------------------- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Flo" <Florent.Boulanger (AT) WJMC (DOT) org> wrote in message news:#PuQz5jSDHA.1556 (AT) TK2MSFTNGP10 (DOT) phx.gbl... I am trying to develop an automatic synchronization between 2 tables and run it once a day. The fields of the Source Table are : SSN, first, last, phone, The fields of the Destination Table are : IT_ID, SSN, first, last, phone, email (the same 5 fields + a unique ID) During this synchronization, I would like to: a.. delete records in my destination table if they have been deleted in my source table b.. insert records in my destination table if they have been inserted in my source table c.. update records in my destination table if records are in the source table I heard that the best way to do it is using Data Driven Task, but I don't know how to use them for my matter... Any help is welcome, Thank you, Flo |
#5
| |||
| |||
|
|
OK 1. Set up a datapump task that takes the records from your source to a table at the destination let's say CREATE TABLE MyTable(col1 int PRIMARY KEY, col2 int) 2. Now you set up the 3 Queries 1st the inserts. Imagine the real destination looks just like the table above. INSERT INTO MyRealTable(col1, col2) SELECT col1, col2 FROM MyTable LEFT OUTER JOIN MyRealTable ON MyTable.col1 = MyRealTable.Col1 WHERE MyRealTable.Col1 IS NULL Deletes Again check to see if PK records exist in the Real Table but not in the Source table and delete them Updates Update the columns in the Real table wjere there is a correspondin PK record in the Source table -- ---------------------------- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Flo" <Florent.Boulanger (AT) WJMC (DOT) org> wrote in message news:#XwcbMvSDHA.1948 (AT) TK2MSFTNGP12 (DOT) phx.gbl... Thanks a lot Allan! My problem now is that I don't know how to fire these 3 queries and with the corresponding query? Thanks again, Flo "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message news:%23l3NJppSDHA.1920 (AT) TK2MSFTNGP11 (DOT) phx.gbl... If you are DTSing over the whole table then you simply pump the whole table across. You then drop onto the designer surface 3 ExecuteSQL tasks 1. INSERTS INSERT INTO DESTTABLE(col list) SELECT col list FROM SOURCETABLE S LEFT OUTER JOIN DESTTABLE D ON S.Key = D.Key WHERE D.Key IS NULL 2. DELETES DELETE D FROM DESTTABLE D LEFT OUTER JOIN SOURCETABLE S ON D.Key = S.Key WHERE S.Key IS NULL 3. UPDATES UPDATE D SET........................... FROM DESTTABLE D JOIN SOURCETABLE S ON D.Key = S.Key -- ---------------------------- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Flo" <Florent.Boulanger (AT) WJMC (DOT) org> wrote in message news:OaQf$5lSDHA.2148 (AT) TK2MSFTNGP11 (DOT) phx.gbl... I forgot to mention that I have about 2000 records so I think I could DTS over the whole lot and not having a trigger on the source. My problem is that I don't know how? I am using DTS designer and I don't know if I have to use a special query on the source? I didn't specify any. I specified the following transformation: Function Main() Select Case Trim(DTSSource("employee")) Case "New" Main = DTSTransformStat_InsertQuery Case "Change" Main = DTSTransformStat_UpdateQuery Case "Delete" Main = DTSTransformStat_DeleteQuery Case Else Main = DTSTransformStat_SkipRow End Select End Function And then for the insert I specified something like that: INSERT INTO dest_table (SSN, FIRST_NAME, LAST_NAME, WORK_PHONE) VALUES (?, ?, ?, ?) But the parameters are values of the destination table instead of of the source table. Thank you, Flo "Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message news:OdqtyclSDHA.1748 (AT) TK2MSFTNGP11 (DOT) phx.gbl... OK What you would do is this Have a trigger on the Source. it logs to a table the INSERT/UPDATE/DELETE. You would generally have a field in there as well as a flag with values of U,I,D for obvious reasons. You would then use this to determine through the Data Driven Query Task what to do with the data. If there are not too many records in the source it may be quicker to DTS over the whole lot Then use TSQL to do it Key in Source not in Dest = INSERT Key in DEST not in Source = DELETE Key in both = UPDATE -- ---------------------------- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Flo" <Florent.Boulanger (AT) WJMC (DOT) org> wrote in message news:#PuQz5jSDHA.1556 (AT) TK2MSFTNGP10 (DOT) phx.gbl... I am trying to develop an automatic synchronization between 2 tables and run it once a day. The fields of the Source Table are : SSN, first, last, phone, The fields of the Destination Table are : IT_ID, SSN, first, last, phone, email (the same 5 fields + a unique ID) During this synchronization, I would like to: a.. delete records in my destination table if they have been deleted in my source table b.. insert records in my destination table if they have been inserted in my source table c.. update records in my destination table if records are in the source table I heard that the best way to do it is using Data Driven Task, but I don't know how to use them for my matter... Any help is welcome, Thank you, Flo |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.