CODEWITHSUNDEEP
sql-serverappendunion-all
Sam
Sam

Reputation: 25

How to 'Union all' two tables and the result to update the second table

I'm trying to UNION ALL (fully join both tables (they have the same column names)) two tables together but update the second table with the result of the union.

The code below gets the result I want but doesn't update table 2 with the result

SELECT * FROM table 2

UNION ALL

SELECT * FROM table 1

For example:

Table 2

Diagram name Column1 Column2 (tons more column names)
Name1 Data1 Data1
Name2 Data2 Data2
Name3 Data3 Data3

table 1

Diagram name Column1 Column2 (tons more column names)
Name4 Data4 Data4
Name5 Data5 Data5
Name6 Data6 Data6

End result (that I want table 2 to look like)

Diagram name Data1 Data2 (tons more column names)
Name1 Data1 Data1
Name2 Data2 Data2
Name3 Data3 Data3
Name4 Data4 Data4
Name5 Data5 Data5
Name6 Data6 Data6

Upvotes: -1

Views: 538

Answers (4)

Esperento57
Esperento57

Reputation: 17492

If you don't want duplicates, you could eliminate those with not exists (same if there are like a 100 column headers), condition: table1 and table2 must have same columns count and same type of columns:

insert into table2
select distinct * from table1
except
select * from table2

Upvotes: 1

sgeddes
sgeddes

Reputation: 62861

Since union all doesn't remove duplicates, this would produce the same result:

insert into table2 (diagram, col1, col2)
select diagram, col1, col2 
from table1 t1

If you don't want duplicates, you could eliminate those with not exists:

insert into table2 (diagram, col1, col2)
select diagram, col1, col2 
from table1 t1 
where not exists (
    select 1
    from table2 t2 
    where t1.diagram != t2.diagram and t1.col1 != t2.col1 and t1.col2 != t2.col2

)

Upvotes: 4

Pm Duda
Pm Duda

Reputation: 740

If you want to update table 2 with union all of table 1 and table 2, isn't it the same as inserting rows from table 1 to table 2?

Insert into table 2
select [Diagram name], [Data1], [Data2] from table 1

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522516

I think you just want to insert the records from table 1 into table 2:

INSERT INTO table2 ([Diagram name], [Data1], [Data2])
SELECT [Diagram name], [Data1], [Data2])
FROM table1;

Note that your current query does produce the intermediate result you want. If you wanted to populate/create a new table using that query, it would work. But the issue is that table 2 already has data in it.

Upvotes: 2

Related Questions