SQL：将一个表中的所有记录插入到另一个表中，而不指定列

SQL: Insert all records from one table to another table without specific the columns

提问by Hyman

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.

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

回答by HLGEM

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 * 不应用作插入的基础，因为列可能会四处移动并破坏您的插入（或者更糟的是不会破坏您的插入但会弄乱您的数据。假设有人在 select 中向表中添加了一列，而不是另一个表，你的代码会中断。或者假设某人，出于超出理解但经常发生的原因，决定在表上进行删除并重新创建并将列移动到不同的顺序。现在你的姓氏是名字的地方in original 和 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).

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

回答by ragamufin

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

根据其他帖子：插入 a... 的所有值，您可以执行以下操作：

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.

指定其他答案所指示的列名称很重要。

回答by nitinuniyal

Use this

用这个

SELECT *
INTO new_table_name
FROM current_table_name

回答by Michael Todd

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 导入和导出向导。右键单击要在其中移动数据的数据库，然后单击“导出”以访问它。

回答by Skeolan

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 INTOclause does exist in SQL Server 2000-2005, but still requires specifying column names. 2008 appears to add the ability to use SELECT *.

INTOSQL 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 子句仅在目标表尚不存在时才起作用。如果您要向现有表添加记录，这将无济于事。

回答by iamdoubz

All the answers above, for some reason or another, did not work for me on SQL Server 2012. My situation was I accidently deleted all rows instead of just one row. After our DBA restored the table to dbo.foo_bak, I used the below to restore. NOTE:This only works if the backup table (represented by dbo.foo_bak) and the table that you are writing to (dbo.foo) have the exact same column names.

出于某种原因，上面的所有答案在 SQL Server 2012 上对我不起作用。我的情况是我不小心删除了所有行而不是一行。在我们的 DBA 将表恢复到 后dbo.foo_bak，我使用下面的来恢复。注意：这仅在备份表（由 表示dbo.foo_bak）和您要写入的表 ( dbo.foo) 具有完全相同的列名时才有效。

This is what worked for me using a hybrid of a bunch of different answers:

这就是使用一堆不同答案的混合对我有用的方法：

USE [database_name];
GO
SET IDENTITY_INSERT dbo.foo ON;
GO
INSERT INTO [dbo].[foo]
           ([rown0]
           ,[row1]
           ,[row2]
           ,[row3]
           ,...
           ,[rown])
     SELECT * FROM [dbo].[foo_bak];
GO
SET IDENTITY_INSERT dbo.foo OFF;
GO

This version of my answer is helpful if you have primary and foreign keys.

如果您有主键和外键，我的这个版本的答案会很有帮助。

回答by Rashami Ranjan Biswal

You could try this:

你可以试试这个：

SELECT * INTO foo FROM foo_bk

回答by itayw

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 optionthat 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 :)

我希望这有帮助 ：）