October 24, 2006 at 2:20 pm
I have two databases(one on MYSQL, the other on SQL server 2000). The MYSQL db contains user information that I would like to use to update some of the columns on the SQL server 2000. Problem that I am having is when I use DTS, it will do an append(add new records) to the table or it will delete all records and update. I would like to be able to retain the records on the SQL server 2000 while updating a few columns with the information contained in the MYSQL db.
Is there away to do this using DTS? I have looked at some of the suggestions for scripting, but just getting more and more confused. Would appreciate an quick exmaple script, if that is the way to go.
Cheers
Michael
October 24, 2006 at 5:00 pm
Michael,
My suggestion is to import the data into a staging table in SQL Server then use T-SQL to update what you want in the destination table. I assume both tables have key columns to match on.
You can't do this using the import/export wizard. You'll have to create a DTS package with a Transform Data task to import into the staging table and an Execute SQL task to update the destination table.
The update statement would be something like
update desttab
set desttab.col1 = sourcetab.col1
from sourcetab inner join desttab
on sourcetab.keycol = desttab.keycol
Greg
Greg
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply