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 |
|
PaulTeal
Yak Posting Veteran
67 Posts |
 Posted - 2003-04-02 : 17:08:37
|
| I am exporting data from a SQL Server view to an Access database using DTS. The view is created to match the Access database table exactly (even column names). The only difference between the two is that the Access database has an additional column which is an autonumber.I want to append the existing data with the new data, so that the Access database has both its existing data and the new data from the export. I want to let the Access database autonumber each row to maintain uniqueness.When I execute the package it fails. The error says that the insert will create duplicate values in the primary key.I changed the properties of the data pump to enable identity inserts, but this has not helped.Any ideas?Paul Tealpaul@partytilyoupop.com |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-02 : 18:19:10
|
| You will probably have to use a SQL statement in the DTS package. So instead of selecting the option that says "Copy table(s) and view(s) from the source database", select the one that says "Use a query to specify the data to transfer". Your SQL statement would exclude the autonumber column. For instance,INSERT INTO AccessTable (Column2, Column3, Column4)SELECT SomeColumn1, SomeColumn2, SomeColumn3FROM SQLServerTableLet's assume that the autonumber column is called Column1 in the AccessTable table. By excluding it, you are allowing Access to do the work for you for that column.HTH,Tara |
 |
|
|
|
|
|
SQL Server to Access Export Failure