SQL：将所有记录从一个表插入另一个表而不指定列

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

我想将备份表foo_bk中的所有记录插入foo表而不指定列。

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.

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

6 个解决方案

#1

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.

你不应该想要这样做。选择*不应该用作插入的基础，因为列可能会被移动并破坏插入（或者更糟糕的是不会破坏插入但是会弄乱你的数据。假设有人在select中添加了一个列而不是在其他表中，你的代码会中断。或者假设有人因为超出理解但经常发生的原因决定进行删除并在表上重新创建并将列移动到不同的顺序。现在你的last_name是第一个名字是在原来和select *会将它放在另一个表中的错误列中。如果无法指定列，并且无法在您感兴趣的表中指定一列到您想要的列的特定映射，那么这是一种非常糟糕的做法。

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).

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

#2

Per this other post: Insert all values of a..., you can do the following:

根据这篇文章：插入...的所有值，您可以执行以下操作：

INSERT INTO new_table (Foo, Bar, Fizz, Buzz)
SELECT Foo, Bar, Fizz, Buzz
FROM initial_table

It's important to specify the column names as indicated by the other answers.

指定列名称非常重要，如其他答案所示。

#3

Use this

用这个

SELECT *
INTO new_table_name
FROM current_table_name

#4

You need to have at least the same number of columns and each column has to be defined in exactly the same way, i.e. a varchar column can't be inserted into an int column.

您需要至少具有相同数量的列，并且必须以完全相同的方式定义每个列，即无法将varchar列插入到int列中。

For bulk transfer, check the documentation for the SQL implementation you're using. There are often tools available to bulk transfer data from one table to another. For SqlServer 2005, for example, you could use the SQL Server Import and Export Wizard. Right-click on the database you're trying to move data around in and click Export to access it.

对于批量传输，请查看您正在使用的SQL实现的文档。通常有工具可用于将数据从一个表批量传输到另一个表。例如，对于SqlServer 2005，您可以使用SQL Server导入和导出向导。右键单击要尝试移动数据的数据库，然后单击“导出”以访问它。

#5

SQL 2008 allows you to forgo specifying column names in your SELECT if you use SELECT INTO rather than INSERT INTO / SELECT:

如果使用SELECT INTO而不是INSERT INTO / SELECT，SQL 2008允许您放弃在SELECT中指定列名：

SELECT *
INTO Foo
FROM Bar
WHERE x=y

The INTO clause does exist in SQL Server 2000-2005, but still requires specifying column names. 2008 appears to add the ability to use SELECT *.

INTO子句确实存在于SQL Server 2000-2005中，但仍需要指定列名。 2008似乎增加了使用SELECT *的能力。

See the MSDN articles on INTO (SQL2005), (SQL2008) for details.

有关详细信息，请参阅有关INTO（SQL2005），（SQL2008）的MSDN文章。

The INTO clause only works if the destination table does not yet exist, however. If you're looking to add records to an existing table, this won't help.

但是，INTO子句仅在目标表尚不存在时才有效。如果您要将记录添加到现有表中，这将无济于事。

#6

As you probably understood from previous answers, you can't really do what you're after. I think you can understand the problem SQL Server is experiencing with not knowing how to map the additional/missing columns.

正如你可能从之前的答案中所理解的那样，你无法真正做到你所追求的。我认为你可以理解SQL Server遇到的问题而不知道如何映射其他/缺少的列。

That said, since you mention that the purpose of what you're trying to here is backup, maybe we can work with SQL Server and workaround the issue. Not knowing your exact scenario makes it impossible to hit with a right answer here, but I assume the following:

也就是说，既然你提到你在这里尝试的目的是备份，也许我们可以使用SQL Server并解决问题。不知道你的确切场景使得无法在这里找到正确的答案，但我假设如下：

You wish to manage a backup/audit process for a table.
您希望管理表的备份/审计过程。
You probably have a few of those and wish to avoid altering dependent objects on every column addition/removal.
您可能有一些这些并希望避免在每个列添加/删除时更改依赖对象。
The backup table may contain additional columns for auditing purposes.
备份表可能包含其他列以用于审计目的。

I wish to suggest two options for you:

我想为您推荐两种选择：

The efficient practice (IMO) for this can be to detect schema changes using DDL triggers and use them to alter the backup table accordingly. This will enable you to use the 'select * from...' approach, because the column list will be consistent between the two tables.

对此的有效实践（IMO）可以是使用DDL触发器检测模式更改，并使用它们相应地更改备份表。这将使您能够使用'select * from ...'方法，因为列表将在两个表之间保持一致。

I have used this approach successfully and you can leverage it to have DDL triggers automatically manage your auditing tables. In my case, I used a naming convention for a table requiring audits and the DDL trigger just managed it on the fly.

我已成功使用此方法，您可以利用它来让DDL触发器自动管理您的审计表。在我的例子中，我使用了一个需要审计的表的命名约定，而DDL触发器只是在运行时管理它。

Another option that might be useful for your specific scenario is to create a supporting view for the tables aligning the column list. Here's a quick example:

对您的特定方案可能有用的另一个选项是为对齐列列表的表创建支持视图。这是一个简单的例子：

create table foo (id int, name varchar(50))
create table foo_bk (id int, name varchar(50), tagid int)
go

create view vw_foo as select id,name from foo 
go

create view vw_foo_bk as select id,name from foo_bk
go

insert into vw_foo
    select * from vw_foo_bk 
go

drop view vw_foo
drop view vw_foo_bk
drop table foo
drop table foo_bk
go

I hope this helps :)

我希望这有帮助：）

#1

#2