Copy data from one table to another using the SQL UPDATE statement

Question

UPDATE table2 SET table2.col1 = table1.col1, table2.col2 = table1.col2, table2.col3 = table2.col3, ... FROM table1, table2 WHERE table1.memberid = table2.memberid 

Please help me understand how to implement the SET clause when there are 9-10 rows, and with the common column name SCRIPT_ID, so that the script can be used again in the future to update the same table.

Here is a snippet from the table:

 ____ _____________ __________________ _____ _ _____ 999 EMS02075SVC Host Controller 15099 3 60000 1000 EMS02075SVC DSM Controller 15099 1 60000 1001 EMS02075WEB1 Application Server 4447 1 60000 

Answer 1

This will work if your source and target tables are identical and have the same members (which, I believe, are primary):

 UPDATE destination SET destination.col1 = source.col1, destination.col2 = source.col2, destination.col3 = source.col3, ... FROM table1 AS source JOIN table2 AS destination ON source.memberid = destination.memberid 

If your source and destination tables are identical, but your source has new rows (records) that the addressee lacks, you will need a selective INSERT :

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

The above will only insert records that are not yet listed in your destination table. Since you are using SQL Server 2008, you can try playing with the MERGE , which should handle both situations and everything that you put into it in one set of code.