Copy data from one table onto another using 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 to understand how to implement SET clause when there are 9-10 rows, and with common column name SCRIPT_ID so that 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 destination tables are identical and have the same memberids (which I assume are primary keys):

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 destination lacks, you'll need an selective INSERT statement:

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 already in your destination table. Since you're using SQL Server 2008, you might try playing with the MERGE statement which should handle both situations and whatever else you code into it in one set of code.