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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-07-06 : 11:54:54
|
| prakash writes "I have an ODBC Connection to MySQL Database and using it with DTS to update the Local MS-SQL Server. Each time DTS is scheduled to execute , i have DTS set to delete the existing records and import the new records to the table( i mean a Single Table) from MySQL Server. If i set a primary key for th e table in MS SQL server and then when i import from MySQL will skip all the records which is already there in Local MS-SQL Server or is there any other way to update only the newly upated records MySQL Serverany ideas, let me knowthanks prakash" |
|
|
vsrajan76
Starting Member
10 Posts |
Posted - 2004-07-08 : 17:45:15
|
| First create a Temp import table (Similar to that of the original table). Import all the data into that table first. Create a DTS package like this..Create Procedure UpdateNewRecs as set ANSI_NULLS ONBEGIN TRANSACTIONSET TRANSACTION ISOLATION LEVEL READ COMMITTED set nocount on insert into MainTable select * from ImportTable as I where not exists ( select * from MainTable as P (P.col1 = I.col1)) --...in where clause list all the primary key fieldscommitBEGIN TRANSACTIONSET TRANSACTION ISOLATION LEVEL READ COMMITTED set nocount on update MainTable set col1 = I.col1..from MainTable as P, ImportTable as I where (P.col1= I.col1) --...in where clause list all the primary key fieldscommitBEGIN TRANSACTIONSET TRANSACTION ISOLATION LEVEL READ COMMITTED set nocount on Delete From MainTable Where not exists (Select * From ImportTable as I where (MainTable.Col1 = I.col1) commitGOCall the Store Procedure in a DTS PackageYou can even schedule the DTS to do this in a regular intervalHope this helps!!!- Sundar |
 |
|
|
|
|
|
DTS Question