SQL:将一个表中的所有记录插入到另一个表中，而不指定列 [英] SQL: Insert all records from one table to another table without specific the columns

问题描述

我想将备份表 foo_bk 中的所有记录插入到 foo 表中，但不指定列.

如果我尝试这个查询

INSERT INTO foo选择 *从 foo_bk

我会收到错误插入错误:列名或提供的值的数量与表定义不匹配."

是否可以在不提供列名的情况下从一个表批量插入到另一个表?我已经谷歌它，但似乎无法找到答案.所有答案都需要特定的列.

解决方案

您不应该想要这样做.Select * 不应用作插入的基础，因为列可能会四处移动并破坏您的插入(或者更糟的是不会破坏您的插入但会弄乱您的数据.假设有人在 select 中向表中添加了一列，但不是另一个表，你的代码会中断.或者假设某人，出于超出理解但经常发生的原因，决定在表上进行删除并重新创建并将列移动到不同的顺序.现在你的姓氏是名字的地方in original 和 select * 会将它放在另一个表中的错误列中.未能指定列以及一列到您感兴趣的表中您想要的列的具体映射是一种非常糟糕的做法.

现在你可能有几个问题，首先这两个结构不直接匹配，或者第二个被插入的表有一个标识列，所以即使可插入的列是直接匹配，被插入的表也有一个列比另一列多，并且不指定数据库假定您将尝试插入到该列.或者，您可能有相同数量的列，但其中一个是身份，因此无法插入(尽管我认为那将是不同的错误消息).

I want to insert all the record from the back up table foo_bk into foo table without specific the columns.

if i try this query

INSERT INTO foo 
SELECT *
FROM foo_bk

i'll get error "Insert Error: Column name or number of supplied values does not match table definition."

Is it possible to do bulk insert from one table to another without supply the column name? I've google it but can't seem to find an answer. all the answer require specific the columns.

解决方案

You should not ever want to do this. Select * should not be used as the basis for an insert as the columns may get moved around and break your insert (or worse not break your insert but mess up your data. Suppose someone adds a column to the table in the select but not the other table, you code will break. Or suppose someone, for reasons that surpass understanding but frequently happen, decides to do a drop and recreate on a table and move the columns around to a different order. Now your last_name is is the place first_name was in originally and select * will put it in the wrong column in the other table. It is an extremely poor practice to fail to specify columns and the specific mapping of one column to the column you want in the table you are interested in.

Right now you may have several problems, first the two structures don't match directly or second the table being inserted to has an identity column and so even though the insertable columns are a direct match, the table being inserted to has one more column than the other and by not specifying the database assumes you are going to try to insert to that column. Or you might have the same number of columns but one is an identity and thus can't be inserted into (although I think that would be a different error message).

这篇关于SQL:将一个表中的所有记录插入到另一个表中，而不指定列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！