Select from three tables - insert into new table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select from three tables - insert into new table

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2010-03-08 : 08:13:43

I have three tables that I want to join into a new table, the problem is that the same primary key data existst in the different tables (table1, table2 and table3).
I only want to have one and only one row to insert into targetTable.
Primary key id1 and id2 is unique together.

table1
id1, pk
id2, pk
col varchar(10)

table2
id1, pk
id2, pk
col varchar(10)

table3
id1, pk
id2, pk
col varchar(10)

Target table:
targetTable
id1, pk
id2, pk
col varchar(10)

example
table1
id1 id2 col
1 1 test (exists in all tables)
1 2 test

table2
id1 id2 col
1 1 test (exists in all tables)
1 3 test

table3
id1 id2 col
1 1 test (exists in all tables)
1 4 test

Output:
id1 id2 col
1 1 test
1 2 test
1 3 test
1 4 test

I only want the row in table1 to be inserted into the targetTable

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-08 : 08:41:03

[code]INSERT INTO target (id1, id2, col)
SELECT id1, id2, col FROM table1
UNION
SELECT id1, id2, col FROM table2
UNION
SELECT id1, id2, col FROM table3[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources