CODEWITHSUNDEEP
sqlsql-servert-sqlinsert
user2113455
user2113455

Reputation: 5

Insert value from two tables based on the rows source table

I have two tables A and B each with 1 column for simplicity sake and they are primary Keys.

A contains the values (1,2,3) B contains (1,2,3)

The third table needs to be the insertion of both A and B and has a composite primary key.

Table C (id, src)

If the id is coming from table A I'd like src to be 'A' and if its coming from B then 'B'.

There can be duplicate ID's between the tables but they are not the same item which is why I need to create a composite key based on which table the row is coming from. I've tried 

Insert into C (anID, src)
Select
Case when (A.anID is not null) 
  then A.anID else B.anID end,
case when (A.anID is not null) 
  then 'A' else 'B' end
from 
A,
B

But my results always end up as just 3 rows (1, A) (2,A) (3,A) When there should be 6 rows (one of each of those with a B)

Upvotes: 0

Views: 233

Answers (1)

paparazzo
paparazzo

Reputation: 45096

insert into TableC (id, src) 
select ID, 'A' from tableA 
union 
select ID, 'B' from tableB

Upvotes: 1

Related Questions