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 |
|
imughal
Posting Yak Master
192 Posts |
 Posted - 2005-02-01 : 01:34:29
|
| hello,i have to import data from access to sql server. Whenever any changes or updation occurs in access database it should automatically updated to sql server.For this i tried to make DTS import schedule. but its actually append data to sql server. it should only updates changes and add new record in sql server from access. kindly tell me how i can do this.thanks |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-02-01 : 02:07:32
|
| You have several options:1. you could load the access data into a staging table, and then insert only the stuff that is new.2. You could truncate the SQL Serve location, and then load the full amount of data.3. You could find some max value from the SQL Server table, and then use that value in a variable that would be used in your WHERE clause so you only load the delta from access.There are more, but I must ask you why you are doing this? Why don't you just link the access tables to SQL Server. That way, the data goes directly to SQL Server without having to use DTS.-ec |
 |
|
|
rishimainidba
Starting Member
30 Posts |
Posted - 2005-02-01 : 02:23:05
|
| Yes Linked tables through odbc pointing to SQL Server would be best solution in this situation.Rishi |
|
|
|
rishimainidba
Starting Member
30 Posts |
Posted - 2005-02-01 : 23:55:39
|
| One thing u can do out here is use merge replication over heterogeneous source i.e access in this case and configure push subscription to access from SQL Server itself. After doing this any changes made to ur access Database would be reflected to sql server and vice-versa.But for this initially u need to have all ur access Data imported to ur SQL server using DTS or BCP which further can be used as a publisher for access Database.RegardsRishi |
|
|
|
|
|
|
Import data - replication