Appending Tables

[Catadmin]

Quick (or not so) question.

Does anyone know for a fact what happens with a DTS job when it is scheduled to create a destination table? It doesn't allow you the option of choosing "Append to table" unless the table previously exist. So, if you don't check DROP & RECREATE TABLE, and leave it on 'Create Destination Table', what does it do?

For instance, I have 100,000 records in a table on the first day. I run a DTS job to transfer this information to another DB. It sends all 100,000 records.

The next day, I add another 4,000 records and then alter 350 of the first 100,000 records. Does the DTS job re-append all the first 100,000 records or does it only add the 4,000 that were created after the original DTS job? Further more, does it swap out the 350 altered records?


Any insight would be appreciated. Or references. All of my books & BOL don't seem to explain how the scheduled DTS Export does its actual work.




Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[dbomrrsm]

it depends on how the job is set up - if it is set up as above then I feel it will reinsert all the rows - however if you set it up with a query - something along the lines of where the row_id in the destination table doesnt exist in the source table that will do for the new rows.

egarding the updated records in the source table I feel they would need to be flagged during the update and then update destination with records from source and set them equal to the destination row where row_id same and flag is set to yes.

As far as I know SQL Server doesnt have the same function of MERGE as you get in ORACLE - this is a sort of an insert/update where it will insert if it doesnt already exist and update where it does already exist.

Maybe this is coming to SQL Server with 2005 but I'm not the best person to ask about 2005 as I havent seen much of it as yet - but must get round to

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Catadmin]

Job setup would be just a simple DTS export of an entire table, no queries set up with it. I'm mainly curious on how Microsoft had it programmed. I.E., if it did a Truncate Table followed by a blanket Insert statement kind of thing on the back end.


I am wanting to hit one of the RoadTrip SQL 2005 presentations, but not sure if I can get the boss to spring for it. Apparently, they're hyping the heck out of the new 2005 features.

Http://www.windowsitpro.com/

has more details on that (the link is on the far right side, halfway down the page.





Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!