在 SQL 中合并 2 个表并保存到 1 个新表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26750410/
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
Merge 2 tables in SQL and save into 1 new table
提问by Chubaka
If we have two or more tables with the same columns
如果我们有两个或多个具有相同列的表
Table 1
表格1
Structure, Name, Active
1,A,1
Table 2
表 2
Structure, Name, Active
2,B,0
We would like to combine these two tables and save it into a new one
我们想将这两个表合并并保存到一个新的表中
New Table
新表
Structure, Name, Active
1,A,1
2,B,0
Here is the code
这是代码
CREATE TABLE Amide_actives_decoys
(
Structure NVARCHAR(255),
Name NVARCHAR(255),
Active INT
)
GO
INSERT Amide_actives_decoys
FROM (
SELECT * FROM Amide_decoys
UNION
SELECT * FROM Amide_actives
)
The following error message will show up
将显示以下错误消息
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'FROM'.
消息 156，级别 15，状态 1，第 10 行
关键字“FROM”附近的语法不正确。
The same thing if we use
同样的事情，如果我们使用
SELECT * INTO Amide_actives_decoys
FROM (
SELECT * FROM Amide_decoys
UNION
SELECT * FROM Amide_actives
)
Following this answer
按照这个答案
Joining a table onto itself in SQL and saving the result
The error message will be
错误消息将是
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ';'.
消息 102，级别 15，状态 1，第 5 行
“;”附近的语法不正确。
Could any guru kindly offer some comments? Thanks!
任何大师都可以提供一些意见吗？谢谢！
回答by Multisync
This syntax works in different databases:
此语法适用于不同的数据库：
INSERT INTO Amide_actives_decoys(Structure, Name, Active)
SELECT * FROM Amide_decoys
UNION
SELECT * FROM Amide_actives;
In this form of INSERT, the output of the subquery becomes the set of input values for the INSERT.
Note that the datatypes for the expressions in the SELECT statement subquery must match the datatypes in the target table of the INSERT statement.
All of the rows returned by the subquery are inserted into the Amide_actives_decoys table.
If any one row fails the INSERT due to a constraint violation or datatype conflict, the entire INSERT fails and no rows are inserted.
Any valid subquery may be used within the INSERT statement.
在这种形式的 INSERT 中，子查询的输出成为 INSERT 的输入值集。
请注意，SELECT 语句子查询中表达式的数据类型必须与 INSERT 语句的目标表中的数据类型匹配。
子查询返回的所有行都插入到 Amide_actives_decoys 表中。
如果任何一行由于违反约束或数据类型冲突而导致 INSERT 失败，则整个 INSERT 失败并且不会插入任何行。
任何有效的子查询都可以在 INSERT 语句中使用。
回答by Simon1979
I think you need to UNION ALLotherwise you may not capture all the data; depends on what data is in the table (duplicates etc).
我认为您需要这样做，UNION ALL否则您可能无法捕获所有数据；取决于表中的数据（重复等）。
INSERT INTO Amide_actives_decoys(Structure, Name, Active)
SELECT * FROM Amide_decoys
UNION ALL
SELECT * FROM Amide_actives;
回答by King_Fisher
The General syntax is
一般语法是
INSERT INTO table2
SELECT * FROM table1;
you can SELECT INTO Statement in this Case
在这种情况下，您可以 SELECT INTO 语句
with cte as (select 1 col1 ,2 col2
union all
select 2,3)
select * into #tabletest from cte
select *From #tabletest
回答by Jithin Shaji
In both your answers, the issue is that you have not given an alias name for the table as a result.I think you missed an 'INTO' in the INSERTstatement as well.
在您的两个答案中，问题在于您没有为表提供别名。我认为您也错过INTO了INSERT语句中的“ ” 。
Query 1:
查询 1：
CREATE TABLE Amide_actives_decoys
(
Structure NVARCHAR(255),
Name NVARCHAR(255),
Active INT
)
GO
INSERT INTO Amide_actives_decoys
SELECT *
FROM (
SELECT * FROM Amide_decoys
UNION
SELECT * FROM Amide_actives
) LU --LU is added.
For Query 1, the below also is correct
对于查询 1，以下内容也是正确的
INSERT INTO Amide_actives_decoys
SELECT * FROM Amide_decoys
UNION
SELECT * FROM Amide_actives
Query 2:
查询 2：
SELECT *
INTO Amide_actives_decoys
FROM (
SELECT * FROM Amide_decoys
UNION
SELECT * FROM Amide_actives
) LU -- LU added
回答by user6704385
create table Amide_actives_decoys
as
select Structure, Name, Active from
(
SELECT * FROM Amide_decoys
UNION
SELECT * FROM Amide_actives
)
;
