Add column to table and populate from another table

Question

I have a table that I need to append a column to. The data for the column will come from a new table that is related by the customer number. So I'll create col B in the existing table. I want to populate it with the related data from col C of another table. What is the best way to accomplish this?

Answer 1

Try something like this:

alter table Table1 add B int null

update t1
set B = t2.C
from Table1 t1 inner join Table2 t2 on t2.customer_number = t1.customer_number

In this example, the columns B and C are integer numbers.

Answer 2

Hi @Dom

Seems like what you need an UPDATE.

Check this sample:

CREATE TABLE Table1 (ID INT,ColA INT)
INSERT INTO Table1 VALUES (1,111),(2,222),(3,333),(4,444)

CREATE TABLE Table2(ID INT,ColC VARCHAR(20))
INSERT INTO Table2 VALUES (1,'AAA'),(2,'BBB'),(3,'CCC'),(4,'DDD'),(5,'EEE')

-- adding a new column 
ALTER TABLE Table1 ADD ColB VARCHAR(20) NULL;
GO
--UPDATE
UPDATE T1
SET T1.ColB = T2.ColC
FROM Table1 T1 JOIN Table2 T2 ON T2.ID=T1.ID;

DROP TABLE table1,table2

Also, if you have values of id in table2 that don't appear in table1 and you want to insert those values into table1 as well you could try MERGE:

--MERGE
MERGE Table1 AS T
USING TAble2 AS S ON S.ID = T.ID
WHEN MATCHED THEN
    UPDATE 
    SET ColB = ColC
WHEN NOT MATCHED THEN
    INSERT (id,ColB)
    VALUES(S.ID,S.ColC);

Refer to this article for more details: Understanding the SQL MERGE statement

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.