May 19, 2002 at 10:50 pm
Hello all,
Anybody knows how to transfer data from Access database to SQL database using DTS. If data already exist in SQL database, will only do update. If new data, will do insert.
thanks before ...
Hendry
May 20, 2002 at 4:23 am
Using the Import Wizard it will do insert only and any unique constraints that are broken will cause this to fail. You can however handle a few ways using a DTS package.
1) Pull into a staging table that you can use to clean incoming data and check against the existing table to handle updates and new inserts only.
2) Create a linked server to the access database, then you can query the data for updates and new inserts, also does not require DTS.
3) Use OPENROWSET to connect to the access database, working is similar to option 2.
4) Use DTS to make the Access connection and write the logic to find updates and new inserts in the package.
I personally would go with 1 or 3 myself unless the Access DB will be in a static location then I may opt for option 2.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 20, 2002 at 5:03 am
You could also look at replication as a way of doing this.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply