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

user_9510022 0
Viewed 5175

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:
enter image description here

table2 looks like this:
enter image description here

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?

ssms tsql sql-server sql
5 Answers

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 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:

UPDATE t2
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);
user_20299830

Description: you need an UPDATE, not an INSERT. The former will add a new row, hence your error for non-nullable columns

Try the following to test, and if it looks good, run the UPDATE statement:

SELECT 
     t2.*
    ,table1.programs_total   /* new column*/
FROM table2 t2
LEFT OUTER JOIN table1 on t2.ID = table1.id  ;

UPDATE:

UPDATE t2
SET total_programs = table1.programs_total
FROM table2 t2
LEFT OUTER JOIN table1 on t2.ID = table1.id  ;
user_35938080

Firstly you can get the data with:

SELECT 
    table2.id,
    table2.total_duration,
    table1.programs_total
FROM
    table2
    LEFT JOIN table1
    ON table2.id = table1.id

And if you need this data as a new table, you can simply add an INTO like this:

SELECT 
    table2.id,
    table2.total_duration,
    table1.programs_total
INTO
    table3
FROM
    table2
    LEFT JOIN table1
    ON table2.id = table1.id

With 'table3' being the name of the new table.

user_133790500

You need to update that column:

UPDATE table2 
SET total_programs = B.total_programs
FROM table1 A
JOIN table2 B
ON A.ID = B.ID
user_80581680

Thanks for asking question with such details. You can also use subquery to update table2.

update table2
set programs_total=(select programs_total from table1 where table1.id=table2.id)
user_86516010
Related