DTS for updating and inserting rows from MS-Access to sql server.
-
I have one access table. I want to select few columns from Access and want to export to Sqlserver.
If recordid column (lrsn column in access) already exists in sql server, I want to update those rows. If not I want to insert those new records. How can I do this?
-
Use Correlated Subqueries to solve the problem.
Using Correlated Subqueries, just match the source and target columns.
If rows exists, do the update else insert.
For this you can use sql task in DTS. In your sql task statement, first u have to update the records as in one go. then do the insert using not exist condition(using sub query.)
Saravanan.
Kindest Regards,Saravanan V
geocities.com -
How do I make connections from source to target. What transformations do i have to specify.(Copy column, Activex script.) If Activex Script How to write it. Because I am new to sql server
-
Right click on the SQL Server table
select All tasks - click on arrow
select Import Data
Click Next
From Data Source drop down select Microsoft Access (yellow key beside it - assuming you are using MS Office 2000)
Select Access file in the "file name column"
click Next
enter server name,username,password,name of SQL database (assuming you have the necessary privileges)
click Next
Click Next
select "use a query to specify the data to transfer" Option
Click Next
Click on Query Builder option
Under Source tables (on the left), select name of Access table
click on plus sign to expand the table tree
select the fields you want by double clicking them
click Next
Click Next
cilck Next
cilck Next
Select SQL table name under "Destination" by clicking in the white space under "Destination"
Click on ellipses and Transformation
Under Column Mapping tab: select the option you want
Click OK
cilck Next
cilck Next
Click Finish
click Ok
click Done
You should be laughing by now.
Good luck.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply