Continue importing even if duplicate key

[lcorriveau]

I am running a DTS package which imports data from a SQL view from one datasource to a SQL Database on a different SQL server.

It is possible that the SQL view contains records that already exist in the databsae wgere we want to import the data. If the records already exist, I want to ignore those records, and I want the import to continue importing the remaing records.

Any recommondations on how I can acheive this ?

NOTE: I have very little control on the contents of the view, as this view is in at a different site, on a complety different type of databse environment.

Thanks

 

[MaffewW]

Instead of importing the view over which you have no control of the contents, you could import a select statement based on the view.

You would need the remote server set up as a linked server (sp_addlinkedserver) which you may already have, then on your DTS package you could have the selection criteria as something like

select columns from yourview where primarykeycolumn not in
(select primarykeycolumn from remoteserver.remotedb.owner.tablename)

There may be a more efficient way of doing it, especially as I'm using a 'not in' but it should do the trick. Otherwise you may be able to do it with an activex but I couldnt help you with that



Matt

Brighton, UK

 

[SQLSister]

You can use the data driven query task in DTS or import the data to a holding table and then run a query just to import records which don't exist in your table. Something like

INSERT Table1
SELECT * FROM table2 LEFT JOIN table1
ON table2.IDField = table1.IDField
WHERE Table1.IDField is null