SQL 自动匹配 INSERT INTO ... SELECT ... FROM 中的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1787634/
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
Automatically match columns in INSERT INTO ... SELECT ... FROM
提问by Konstantin Spirin
SQL Server question. When doing
SQL Server 问题。做的时候
INSERT INTO T1 SELECT (C1, C2) FROM T2
I don't want to specify column names of T1because they are the same as in T2
我不想指定列名，T1因为它们与T2
Is it possible to do so?
有可能这样做吗？
Currently I'm getting error
目前我收到错误
Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.
Msg 213, Level 16, State 1, Line 1
列名或提供的值数量与表定义不匹配。
采纳答案by Donnie
If T1and T2match exactly you have two choices. You can either selectall columns from T2for the insert into T1, or you can provide a column list to the insertstatement.
如果T1和T2完全匹配，你有两个选择。您可以select从所有列T2的insert into T1，或者你可以提供一个列清单的insert声明。
Even though when you do a selectMSSQL provides column headers that information is not used by an insertstatement to match columns up.
即使当您执行selectMSSQL 提供的列标题时，insert语句也不会使用该信息来匹配列。
回答by Remus Rusanu
Always use explicit columns both in the INSERT and in the SELECT projection. Even if you don't want to, you should:
始终在 INSERT 和 SELECT 投影中使用显式列。即使你不想，你也应该：
INSERT INTO T1 (C1, c2)
SELECT C1, C2 FROM T2
回答by Guffa
Yes, you can omit the field names for the table that you insert to, and you can use select * to get all fields from the table, but I would not recommend this approach.
是的，您可以省略插入到的表的字段名称，并且可以使用 select * 从表中获取所有字段，但我不推荐这种方法。
If you omit the field name the fields are matched by position, not by name. If the fields are not in the exact same order, they will be mixed up. Generally you should avoid relying on the exact layout of the tables, to minimise the risk that changes in the tables breaks the queries.
如果省略字段名称，则字段将按位置匹配，而不是按名称匹配。如果字段的顺序不完全相同，它们将被混淆。通常，您应该避免依赖表的确切布局，以最大限度地减少表中的更改破坏查询的风险。
回答by Sparky
Why not simply
为什么不简单
INSERT INTO t1
SELECT * FROM T2
回答by leepowers
If you're worried about column names you can always alias them:
如果您担心列名，您可以随时为它们取别名：
INSERT INTO T1 (C1, c2)
SELECT C1 AS C1_ALIAS, C2 AS C2_ALIAS FROM T2
Or, more succinctly:
或者，更简洁地说：
INSERT INTO T1 (C1, c2)
SELECT C1 C1_ALIAS, C2 C2_ALIAS FROM T2
Though I can't really think why one would want to in such a simple example
虽然我真的想不出为什么人们会想要在这样一个简单的例子中
回答by ehuehu
First select this sql, chose your table line from sql result and change the target or source tablename. If tables has same columns (same order is not necessary) it will be work.
首先选择此 sql，从 sql 结果中选择您的表行并更改目标或源表名。如果表具有相同的列（不需要相同的顺序），它将起作用。
with xparams as ( select (select user from dual) "OWNER", '' "ADDSTRTOFROMTABLENAME" from dual ) ,t1 as ( SELECT dbat.table_name from dba_tables dbat, xparams where dbat.owner = xparams.OWNER ) ,t1c1 as ( SELECT utcs.table_name , LISTAGG(utcs.column_name,',') within group (order by utcs.column_name) "COLS" from USER_TAB_COLUMNS utcs, t1 where utcs.table_name = t1.table_name group by utcs.table_name ) ,res1 as ( SELECT 'insert into '|| t1c1.table_name || ' ( '|| t1c1.COLS ||') select '|| t1c1.COLS || ' from ' || t1c1.table_name||xparams.ADDSTRTOFROMTABLENAME ||';' "RES" from t1c1, xparams order by t1c1.table_name ) select * from res1
回答by simbo1905
The other answers are good but they don't explain why it is bad to use:
其他答案很好，但它们没有解释为什么使用不好：
INSERT INTO T1
SELECT * FROM T2
In a comment the OP talks about code duplication when specifying columns when using the safer approach:
在评论中，OP 在使用更安全的方法时指定列时谈到了代码重复：
INSERT INTO T1 (C1, c2)
SELECT C1, C2 FROM T2
Yet if you don't be specific you are relying upon the number of columns always matching and the ordering of the columns doing what you expect. That logic will break if one of the tables is altered to add a column.
但是，如果您不具体，您将依赖于始终匹配的列数以及列的排序符合您的预期。如果更改其中一个表以添加列，则该逻辑将中断。
Also you can get into trouble with silent bugs. If you use a tables with the same number of columns but different positions:
您也可能会遇到无声错误的麻烦。如果您使用列数相同但位置不同的表格：
CREATE TABLE tab1 (col1 int, col2 string);
CREATE TABLE tab2 (col1 string, col2 int);
INSERT INTO tab1 values(1, 'aaa');
INSERT INTO TABLE tab2 select * FROM tab1;
Then you might have hoped that you did a copy such that tab1 and tab2 are the same. What I wanted was:
那么你可能希望你做了一个副本，这样 tab1 和 tab2 是相同的。我想要的是：
+-------------------+-------------------+
| tab2.col1 | tab2.col2 |
+-------------------+-------------------+
| 1 | aaa |
+-------------------+-------------------+
But it will load based on column position and cast the data so what I get is:
但它会根据列位置加载并投射数据，所以我得到的是：
+-------------------+-------------------+
| tab2.col1 | tab2.col2 |
+-------------------+-------------------+
| 1 | NULL |
+-------------------+-------------------+
What happened was it could not convert a string into int so it set it to NULL. It could convert the int into a string as '1' which is no longer a number type.
发生的事情是它无法将字符串转换为 int，因此将其设置为 NULL。它可以将 int 转换为不再是数字类型的 '1' 字符串。
Even if the columns do match anyone can do:
即使列确实匹配任何人都可以做到：
ALTER TABLE tab1 ADD COLUMNS (col3 string COMMENT 'a new column');
After that the query that does not specify columns will break saying that the number of columns in the two tables does not match. It will no longer be able to move data into tab2.
之后，未指定列的查询将中断，表示两个表中的列数不匹配。它将不再能够将数据移动到 tab2 中。
This means that the safe thing to do is to be explicit with SQL:
这意味着安全的做法是使用 SQL 显式：
INSERT INTO T1 (C1, c2)
SELECT C1, C2 FROM T2
If someone is just trying to quickly take a copy of a table then some SQL engines support
如果有人只是想快速获取表的副本，那么某些 SQL 引擎支持
CREATE TABLE tab3 AS SELECT * FROM tab1;
In that case sure typing columns is a waste of time and if someone was to add columns to tab1 before you cloned it being explicit would fail to clone the new column. All that counter example shows is that there are no absolute rules in programming only rules of thumb. The rule of thumb for SQL (and any other loosly typed language with implicit conversions) is to be as specific as you can if you don't want silent error at runtime and bugs when someone adds new functionality down the line.
在这种情况下，确定输入列是浪费时间，如果有人在克隆之前将列添加到 tab1，那么显式将无法克隆新列。那个反例表明，在编程中没有绝对的规则，只有经验法则。SQL（以及任何其他具有隐式转换的松散类型语言）的经验法则是尽可能具体，如果您不希望在运行时出现静默错误和在有人添加新功能时出现错误。
回答by ASIK RAJA A
It is Referenced as follows:
参考如下：
INSERT INTO NEWTABLENAME COL1[,COL2,..COLN]
SELECT COL1[,COL2,..COLN] FROM THE EXISTINGTABLENAME
