Use DTS to update a table, not just add data 1

[bartsimpson]

I have two tables, and I need to update one from another every 8 hours. I want to replace the data in the destination table, but I can't do an update, as I can't get a query to update one table from another.

What is the best way to get DTS to perform this task, or is there another method I could use ?

 

[JayKusch]

You could use an "Execute SQL Task" the first command could truncate the destination table, the second could populate it. Something like ...

TRUNCATE TableB

INSERT INTO TableB
  SELECT * FROM TableA

If TableB is in another DB then :

TRUNCATE DB2.dbo.TableB

INSERT INTO DB2.dbo.TableB
  SELECT * FROM DB1.dbo.TableA

If TableB is in another Server and DB then :

TRUNCATE Server2.DB2.dbo.TableB

INSERT INTO Server2.DB2.dbo.TableB
  SELECT * FROM Server1.DB1.dbo.TableA

Thanks

J. Kusch

 

[bartsimpson]

Thanks, J Kusch. I have now done something very similar - I did a

delete
from database table

which deletes everything in that table.

 

[dbomrrsm]

Extract from BOL - Delete will fill up your trans log if you are doing this every 8 hours so if you are deleting all rtows from a table and dont need a log it may be better to use TRUNCATE :

If you want to delete all the rows in a table, TRUNCATE TABLE is faster than DELETE. DELETE physically removes rows one at a time and records each deleted row in the transaction log. TRUNCATE TABLE deallocates all pages associated with the table. For this reason, TRUNCATE TABLE is faster and requires less transaction log space than DELETE. TRUNCATE TABLE is functionally equivalent to DELETE with no WHERE clause, but TRUNCATE TABLE cannot be used with tables referenced by foreign keys. Both DELETE and TRUNCATE TABLE make the space occupied by the deleted rows available for the storage of new data.

DBomrrsm