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?
5条答案
按热度按时间svdrlsy41#
Seems like what you really want an
UPDATE
:If, however, you could have values of
id
intable1
that don't appear intable2
and you want to insert those values intotable2
as well you'll want aMERGE
:Or you could write it as an Upsert as follows:
qojgxg4l2#
Description: you need an
UPDATE
, not anINSERT
. The former will add a new row, hence your error for non-nullable columnsTry the following to test, and if it looks good, run the
UPDATE
statement:UPDATE:
sycxhyv73#
Firstly you can get the data with:
And if you need this data as a new table, you can simply add an INTO like this:
With 'table3' being the name of the new table.
wnavrhmk4#
You need to update that column:
qyyhg6bp5#
Thanks for asking question with such details. You can also use subquery to update table2.