Inserting records where they might already exist?

[Glowworm27]

Hello all,

Hope someone can help with this. I am fairly new to DTS, and found it to be the next best thing to sliced bread, when it comes to import/exporting Data.

No I have a huge database with several years worth of data. I am trying to off-load Old data to some archives. Thats the Easy part.

The hard part is if the user wants to get the data back the import routine is a bit troublesome, what with all the Foreign Key constriants we have on the database.

The trouble I am having is with customers. Our invoices have a one to many relationship with customers. (where by a customer can have multiple invoices) I am off-loading data in daily batches. so each run of the DTS will remove one days worth of invoices. I am off-loading (exporting to csv) all the data and then deleting the data from the database. The customers data may not be deleted if they have other invoices in the system becasue of Foregin key contraints, I do export that customer data to a csv, so if later down the road the other invoices that the customer may have been removed and the customer data is removed, I can get it back.

My trouble comes to re-importing the customer CSV. If the customer does not exist the record is imported fine. but if the customer already exists then the DTS task fails because of the Foregin Key contstraints. I tryed to set the error number to some arbitrary number, and the task still fails, I even set the batch commit to 1 record which makes it work, but determining the number of errors to accept is crazy. I mean I could set the errors to 9999 which should cover most situations, but I was wondering if there would be a better way to re-import this data.

Can I use the lookup function? if so how?

I hope I made sense

thanks


George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[cbhnp]

Try importing the csv file into a staging table first. Then run an insert query like this:

insert into customers
select col1, col2, col3
from customers_stage cs left join customers
  on cs.cust_id = customers.cust_id
where customers.cust_id is null

I'm doing something similar now with moving new and touched records from a production db to a reporting warehouse.

 

[Glowworm27]

yeah , I did something like this, I imported into a staging table, then set a bit field to 1 if they existed in the database, then inserted the records with a 0 (which means they dont exists) and then deleted the rest.

Was just hoping to find a solution using 1 step the transform data task, instead of these 3 steps using sql tasks.

Thanks

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips