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

2ekbmq32 于 2023-02-28 发布在其他

关注(0)|答案(5)|浏览(148)

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?

sql-server

来源：https://stackoverflow.com/questions/67971276/how-to-insert-a-column-from-another-table-in-t-sql

5条答案

按热度按时间

svdrlsy41#

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;

赞(0）回复(0）举报 2023-02-28

qojgxg4l2#

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  ;

赞(0）回复(0）举报 2023-02-28

sycxhyv73#

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.

赞(0）回复(0）举报 2023-02-28

wnavrhmk4#

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

赞(0）回复(0）举报 2023-02-28

qyyhg6bp5#

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)

赞(0）回复(0）举报 2023-02-28

我来回答

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

5条答案

相关问题

热门标签

最新问答