Overwriting Data

[Steve95]

Hi All

Iam new to sql and currently using sql 2000. I have just setup my first DTS. Feels good.....I have scheduled a Job to make the DTS run every day.

What I want to be able to do is, when the DTS runs it overwrites existing data. So in the destination table only new records appear (if any new entries aswell as old entries) or the data set appears as in the data source.

I hope I have made sense. If I have miss anything please ask.

Many Thanks

 

[AlexCuse]

Hi Steve,

You have 2 options, both involve first bringing the data into what's called a staging table (basically a replica of the destination table you have now).

Once this staging table has been loaded, you will need to write a query to either update records in your destination table, or delete rows from your destination table (but ONLY the records that currently exist in your staging table, I assume you have a unique key you can use to perform this check). I would recommend going the update route, as unnecessary deletes and inserts will cause performance degradation for your database.

After the update/delete, you have a second transformation pushing only the rows that are not currently in the destination table. Of course, at the end you will want to empty your staging table so it is ready to go next time.

This is a very high level view of what you need to do, if you have questions about any of the individual steps don't hesitate to ask.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Steve95]

Hi Alex

thanks for above really appreciate it, so my first step is to create a 'staging table', I assume a table where I will output my data after it has been updated ie. run pass the query.

Writing this query for updating, now would that be a view or a 2nd DTS with this query in it?

Many Thanks

 

[AlexCuse]

No, the staging table will be where your data goes before it is moved to its' final destination. Basically, you want to get your source file into an area where you can manipulate it using T-SQL. If the source file is an access table, you might be able to skip this step, but I am not sure. I like to bring it all to the server before making any changes.

You can add a step called an 'Execute SQL Task' to your existing DTS, and put your update query in that. So your steps (all in one DTS) would be

1. Data pump bringing from file source into staging table
2. Updating rows already in destination table to match current column values in staging table.
3. Data pump moving all rows that are not already in destination table (based on comparison of unique keys) into your destination table.

Finally, now is the perfect time to point you to

http://www.sqldts.com,

which has a wealth of information on DTS packages. I think you will find it very helpful, I know I have.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Steve95]

Sorry I made a mis type about the staging table. So the staging table is an actual table and not a holding area with in the DTS where I can manipulate the data and send to my destination table?

 

[AlexCuse]

You can set it up as a temp table if you want (just make sure that it is global (put ## in front of the name rather than a single #)), but I would keep a permanent table in your database.

It basically serves as a holding area to manipulate the data, so you were right there. But you need a physical table somewhere (whether it is temporary not is your choice), so it is not within the DTS but within your database.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom