CODEWITHSUNDEEP
sqlsql-server-2008
A117
A117

Reputation: 339

Insert into with union

I have a problem. There are three tables: T1, T2, T_target. T1 and T2 table have many different columns but I need only the ID column from both. The T_target table has an ID column of course and another: project_No.

There are some IDs which appears in T1 and T2 too, but I don't want to create duplicates between them, if an ID appears in both table it have to be inserted into the T_target only once but if it is already in the T_target it's allowed to act twice. The other of the criteria is every newly inserted ID must be value 21 in 'project_No' column. So, e.g.:

T1:

ID
2548
2566
2569
2843
2888
...

T2:

ID
2557
2566
2569
2700
2913
2994
3018
5426
...

T_target:

ID     project_No
2976   1
3331   7
4049   7
5426   8
5915   3
6253   10
...

And the result I want to see:

T_target:

ID     project_No
2548   21
2557   21
2566   21
2569   21
2700   21
2843   21
2888   21
2913   21
2976   1
2994   21
2018   21
3331   7
4049   7
5426   8
5426   21
5915   3
6253   10
...

So, I tried it with this code (it is important to be here "NOT NULL" criteria because both of T_target columns are primary key):

insert into T_target (ID, project_No)
  select (select ID
  from T1 where ID is not NULL
 union
  select ID
  from T2 where ID is not NULL), 21

select * from T_target

The error message: "Msg 512, Level 16, State 1, Line 2 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated."

Then I tried with VALUES statement instead of the first SELECT and parentheses but the error is the same.

There is a similar problem: mySQL query: How to insert with UNION? but this solution doesn't work for me because it indicates syntax error between VALUE and SELECT.

Please, give me a hand. Thank you!

Upvotes: 24

Views: 143727

Answers (2)

Jared_S
Jared_S

Reputation: 166

I think you have to amend that a little to avoid duplication of the ID's in the select statement.

INSERT INTO T_target
            (ID,
             project_No)

SELECT ID, 21 
FROM (
SELECT ID
FROM   T1
WHERE  ID IS NOT NULL
UNION
SELECT ID
FROM   T2
WHERE  ID IS NOT NULL 
) A

Upvotes: 4

Martin Smith
Martin Smith

Reputation: 453028

This should do what you need

INSERT INTO T_target
            (ID,
             project_No)
SELECT ID,
       21
FROM   T1
WHERE  ID IS NOT NULL
UNION
SELECT ID,
       21
FROM   T2
WHERE  ID IS NOT NULL

Upvotes: 47

Related Questions