CODEWITHSUNDEEP
sql-serversql-update
GLP
GLP

Reputation: 3675

How to update table from another table or excel file?

I have a table T1(col1, col2, info), col1 is primary key. Now I have a another table T2 (col1, col2, col3), col1 is primary key. For example,

T1:

col1 col2 info
num1 data1 info1
num2 data2 info2

T2

col1 col2 col3
num1 data1 Y
num2 data2 N
num3 data3 NULL

How do I write a script to update T1 with T2? The result should be T1:

col1 col2 col3 info
num1 data1 Y info1
num2 data2 N info2
num3 data3 NULL NULL

Also, if my T2 is not a table, is a excel file instead, is it possible to write a script to import the data to T1 and update T1 at the same time?

Upvotes: 1

Views: 465

Answers (1)

Holger Brandt
Holger Brandt

Reputation: 4354

It would be easier to add col3 manually to T1. The update script is a two-parter: an Update and an Insert

UPDATE T1
SET
  T1.col3 = T2.col3  
FROM T1 INNER JOIN T2 ON T1.col1 = T2.col1;

INSERT INTO T1
  (col1, col2, col3, info)
  SELECT T2.col1, T2.col2, T2.col3, NULL 
  FROM T2
  WHERE NOT T2.col1 IN (SELECT col1 FROM T1);

In order for T2 to be an Excel file, you would need to set T2 up as an OLEDB table (using a Linked Server and a MS Jet driver).

Upvotes: 1

Related Questions