How to insert a column from another table in T-SQL?

Question

I have two tables I need to merge without creating a new view/table. Basically, I need to add only one column to an existing table taken from another table.

table1 looks like this:

table2 looks like this:

I need to get a table that would look just like table2 but with an additional column: programs_total. If there is no such id in the first column, I want the second column to have NULL. In this example, I want the raw with id=72_200 to have NULL in the programs_total column.

I tried the following script:

-- adding a new column 
ALTER TABLE table2
ADD programs_total BIGINT NULL;

-- inserting new data
INSERT INTO table2 (programs_total)
SELECT programs_total
FROM table1

but it produces the following error:

Msg 515, Level 16, State 2, Line 4
Cannot insert the value NULL into column 'id', table 'stb.dbo.table2'; column does not allow nulls. INSERT fails.
The statement has been terminated.

I suppose it tries to insert three new rows instead of joining the column with the existing ones. How do I tell it to join the column to the existing rows?

Or maybe I am doing something else wrong?

Answer 1

Seems like what you really want an UPDATE:

UPDATE t2
SET t2.total_programs = t1.total_programs
FROM dbo.table2 t2
     JOIN dbo.table1 t1 ON t2.id = t1.id;

If, however, you could have values of id in table1 that don't appear in table2 and you want to insert those values into table2 as well you'll want a MERGE:

MERGE dbo.Table2 WITH (HOLDLOCK) AS T2 
USING dbo.TAble1 AS T1 ON T2.id = T1.id
WHEN MATCHED THEN
    UPDATE 
    SET total_programs = t1.total_programs
WHEN NOT MATCHED THEN
    INSERT (id,total_programs)
    VALUES(T1.id,T1.total_programs);

Or you could write it as an Upsert as follows:

SET XACT_ABORT;
BEGIN TRANSACTION;

UPDATE t2 WITH (UPDLOCK, SERIALIZABLE) 
SET t2.total_programs = t1.total_programs
FROM dbo.table2 t2
     JOIN dbo.table1 t1 ON t2.id = t1.id;

INSERT INTO dbo.Table2(id,total_programs)
SELECT t1.id,
       t1.total_programs
FROM dbo.Table1 t1
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.Table2 t2
                  WHERE t2.id = t1.id);
COMMIT;