Sql exists copy

Question

Hi, I have a mssql stored procedure question for you experts:

I have two tables [table1] which retrieves new entries all the time. I then have another table [table2] which I need to copy the content of [table1] into.

I need to check if some of the the rows already exists in [table2], if it do, just update the Update-timestamp of [table2], otherwise insert it into [table2].

The tables can be rather big, about 100k entries, so which is the fastest way to do this?

It should be noticed that this is a simplified idea, since there is some more datahandling happening when copying new content from [Table1] -> [Table2].

So to sum up: If a row exist both [Table1] and [Table2] update the timestamp of the row in [Table2] otherwise just insert a new record with the content into [Table1].

Answer 1

If you have SQL Server 2008, it has a MERGE command that can do an insert or update as appropriate.

Answer 2

Given that this sounds like a ETL Process. Have you considered using SQL Server Integration Services?

If you are planning on export/loading/processing lost of data then this is the way to go in my view. You also have the added advantage of being able to run multiple threads in parallel and more options to tweak your data throughput and server memory utilisation etc.

Answer 3

This works across all versions of SQL Server. MERGE does the same in SQL Server 2008.

UPDATE
    table2
SET
    timestampcolumn = whatever
WHERE
    EXISTS (SELECT * 
       FROM table1
       WHERE
           table1.key = table2.key)


INSERT table2 (col1, col2, col3...)
SELECT col1, col2, col3...
FROM table1
WHERE
    NOT EXISTS (SELECT * 
       FROM table2
       WHERE
           table2.key = table1.key)