SQL 用联合插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15160298/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert into with union
提问by A117
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.
我有个问题。共有三个表：T1、T2、T_target。T1 和 T2 表有许多不同的列，但我只需要两者的 ID 列。T_target 表当然有一个 ID 列和另一个：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.:
也有一些 ID 出现在 T1 和 T2 中，但我不想在它们之间创建重复项，如果一个 ID 出现在两个表中，它必须只插入一次 T_target 但如果它已经在 T_target 中允许行动两次。另一个条件是每个新插入的 ID 在“project_No”列中都必须是值 21。所以，例如：
T1:
T1：
ID
2548
2566
2569
2843
2888
...
T2:
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):
所以，我用这个代码尝试了它（这里的“NOT NULL”标准很重要，因为两个 T_target 列都是主键）：
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."
错误消息： “消息 512，级别 16，状态 1，第 2 行子查询返回的值超过 1。当子查询跟随 =、!=、<、<=、>、>= 或子查询是用作表达式。语句已终止。”
Then I tried with VALUES statement instead of the first SELECT and parentheses but the error is the same.
然后我尝试使用 VALUES 语句而不是第一个 SELECT 和括号，但错误是相同的。
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.
还有一个类似的问题： mySQL query: How to insert with UNION？但是这个解决方案对我不起作用，因为它表示 VALUE 和 SELECT 之间的语法错误。
Please, give me a hand. Thank you!
请帮我一把。谢谢！
回答by Martin Smith
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
回答by Jared_S
I think you have to amend that a little to avoid duplication of the ID's in the select statement.
我认为您必须稍微修改一下以避免在 select 语句中重复 ID。
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
