Import improvement | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Import improvement

I have created a Data Transformation Service (DTS) in SQL Server using the import/export data wizard, saved it as a Visual Basic file and then upgraded it to Vb.Net. I am importing data from an excel file into a SQL Server table.
The basics are working fine, but I now want to improve the importing function. I want to disallow duplicate entries so that the same data cannot be entered more than once – except for if the data for a specific record has changed – and in this case I want to add the record, and move its original value to another table. Does anyone have an idea how I can go about this?

Use a permanent working table to upload the data, then create a stored procedure that matches the working table against the data table(s) – using set-based queries will quite probably improve your performance. Okay, not exactly a DTS-based solution but at least one that you can really fine-tune.
It is dts solutions if you put your sp execution into Execute SQL task.
]]>