Importing only current records from flat file

[bustercoder]

Hello,

I have a DTS package which basically imports the contents of a flat file into a table. The problem for me is, by default it is appending to the current records, and I need it to only store the records we're bring in at that time. I'm already archiving the records anyway, but I need the records to be in there long enough to perform actions against. In other words, is there a way to bring in only the records from the flat file, then the next time i bring new records in from the flat file, the existing table records are deleted.

Thanks,
Buster

 

[mtepfer]

Enter an Exeucte SQL TASK to run in your DTS first

TRUNCATE TABLE tablename

This will totally erase the existing table though so if you need any data be sure you have it backed up. Then your new file will be imported

 

[AlexCuse]

Bring your flat file into a staging table.

Join to this staging table to delete records from archive table that you want to replace, using a SQL Task.

Then insert all from staging to archive table.

You only want to truncate if the ENTIRE contents of the table are going to be replaced.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[bustercoder]

Thanks all, great suggestions. Here is the solution I came up with. Before doing what I need to:

/*Delete rows from source, that are already there in the target table*/
DELETE TBZ_Shipments_Import
FROM TBZ_Shipments_Import AS tsi
WHERE EXISTS
(
SELECT 1
FROM TBZ_Shipments AS ts
WHERE ts.order_id = tsi.order_id
)

 

[AlexCuse]

Right on. Looks like we had a little mix-up in your wording, I thought you wanted to delete rows from your final table, which I thought a little odd

One word of advice, I think this query will do the same thing and perform better (if order_id is unique).

DELETE tsi
FROM TBZ_Shipments_Import tsi
inner join TBZ_Shipments ts
on tsi.order_id = ts.order_id

I find it is generally better to use a join than a subquery in the where clause. There are times where a subquery is necessary, but this is not one of them.

hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom