question

Stan avatar image
Stan asked

Does Importing A Text File Using The DTS Wizard Requires Truncate Table?

running on: SQL Server 2000

I am exporting data (csv format) from test DB and import into live DB, so everytime I have to truncate live table and import file. If ther's a key binding, I have to drop the key first, do import, add key back. This task is too inconvenient. How should I improve this? Thanks.

sql-server-2000import-datadts
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

If you have referential constraints on the data that you're importing AND you need to truncate the data, then you will have to drop and recreate the key, there's no way around it. However, I think you're asking if you're required to truncate the data as part of an import? If so, the answer is no. There's no requirement that you truncate the data. As a matter of fact, the best way to create a scalable ETL process is to only move the data that has changed rather than doing a complete reload every time. This is achieved any number of ways. One of the simplest is to maintain an UpdateDate column on the source data and only retrieve the data that has been changed. You'll have to come up with mechanisms to support deletes as well though. There are a number of other ways around this as well.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo avatar image
Leo answered

Hi,

I am not very clear what you trying to achieve. However I think that is what you wanted.

Create - Execute SQL Task You can write 'Truncate statement' and other droping key or whatever you want.

Then add that Task to process before you transfer from CSV file to DB by using Work Flow Properties.

I hope it helps.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.