December 18, 2003 at 3:00 am
Hello all. I am probably missing something obvious but there doesn't seem to be a simple way of getting DTS to copy only the incremental data from one table to another using the Copy SQL Server Objects Task without writing a script to do it. Example:
source_table needs to update destination_table
source_table
A
B
C
D
destination_table
A
B
C
So, I want DTS to leave the existing data in destination_table and simply add the additional data (D) from the source_table.
The options available are Replace existing data (which truncates table first - which I don't want to do) or Append data (which appends all of source table into destination table).
Thanks.
December 18, 2003 at 9:20 am
You should either use a Data Driven Query task with an insert query or an Execute SQL task with an insert query.
The insert query should look something like,
/*Insert rows from source, that are not already in target table*/
INSERT INTO dbo.destination_table
SELECT *
FROM dbo.source_table AS s
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.destination_table AS t
WHERE t.column = s.column
)
I'm currently writing a DTS package to do almost the same thing and I chose to use an Execute SQL task because I've read that DDQ can perform slowly. If you're dealing with a small amount of data, it probably doesn't matter which you use.
Hope this helps,
Greg
Greg
December 18, 2003 at 9:31 am
Thanks Greg that's useful. I just thought that there might be an option within DTS that would do this automatically.
Paul
December 18, 2003 at 9:25 pm
If you have some sort of date or identity col you could use a dynamic task to assign the max value to a global variable before the insert.
The execute sql would then "insert tableB select * from tableA where field > ?" This would be great if it also took advantage of an index in the original table.
December 19, 2003 at 2:00 am
Adding Timestamp columns to your source database is even better.
A timestamp is a unique binary value over the whole database. It increments with 1 bit each time you insert or update a column.
You can even check if columns are updated after the last load.
It's even very performant because a timestamp is 8 bytes and in machine format, you can scan millions of rows in only seconds.
December 19, 2003 at 2:14 pm
Regarding SeekQuel's suggestion of using a Timestamp column for auditing: Wouldn't you actually need to have 2 columns or some other way to record the timestamp value before and after DTS'ing the changes? Otherwise how could you tell if the column had been incremented?
December 22, 2003 at 3:18 pm
DON'T USE DDQ's...they are ridiculously slow.
Use a Data Transformation Task with an "Execute SQL" (instead of a table or a file)source...that's the fastest by far.
"Where not exists" works well, but a left join and looking for a null works better as long as the joining column is unique.
select *
From Table 1
LEFT JOIN Table 2 on 1.UniqueID = 2.UniqueID
where 2.UniqueID is null
Signature is NULL
December 22, 2003 at 3:18 pm
DTT with "Copy Column", to be specific....
Signature is NULL
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply