将数据从一个 SQL Server 表复制到同一数据库中的其他表而不指定列

发布于 2024-10-11 20:54:28 字数 438 浏览 8 评论 0原文

在 SQL Server 中，可以使用以下语句将一个表中的所有数据插入到另一个表中：

INSERT INTO TABLE1 SELECT * FROM TABLE2

在具有标识列的表上运行此命令时，即使我们已经运行了命令 SET IDENTITY_INSERT TABLE1 ON，我们得到错误：

An Explicit value for the Identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON

这意味着我们将必须列出 INSERT 中的所有列才能正常工作。在我们所处的情况下，我们无法访问列名，只能访问需要复制的表的列表。有什么办法可以绕过这个限制吗？

原文

In SQL Server there is the ability to INSERT all of the data from one table into another using the following statement:

INSERT INTO TABLE1 SELECT * FROM TABLE2

When running this on a table with an identity column, even though we have run the command SET IDENTITY_INSERT TABLE1 ON, we get the error:

An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON

This implies that we would have to list off all of the columns within the INSERT in order for this to work properly. In the situation that we are in, we do not have access to the column names, only the list of the tables we need to copy over. Is there any way to get around this limitation?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

放低过去 2024-10-18 20:54:28

您可以通过查询数据字典在运行时动态构建 SQL 语句，如下所示：

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Your_Table'

您可以使用它来动态构建 SQL 语句来执行此插入，并且它将包含其中的所有列。

您可以在 T-SQL 过程中执行上述操作，并且可以使用 sp_executesql 运行动态构建的查询。例如，如果您的查询位于名为 @SQL 的变量中，您可以这样做：

EXECUTE sp_executesql  @SQL

下面给出了一个示例：

create table three (id int identity, other int)
create table four (id int identity, other int)

insert into three select 1
insert into three select 2

declare @sql nvarchar(max)
set @sql = REPLACE(
'
set identity_insert four on
insert into four (:columns:) select :columns: from three
set identity_insert four off',
':columns:',
stuff((
    select ','+quotename(name)
    from sys.columns
    where object_id=object_id('four')-- and is_identity=0
    for xml path('')),1,1,''))
EXECUTE sp_executesql @SQL

You can dynamically build the SQL statement at runtime by querying the data dictionary, like this:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Your_Table'

You could use this to dynamically build the SQL statement to do this insert, and it would have all the columns in it.

You could do the above in T-SQL procedure, and you could use sp_executesql to run the dynamically built query. For example, if your query was in a variable called @SQL, you could do:

EXECUTE sp_executesql  @SQL

An example for this is given below:

create table three (id int identity, other int)
create table four (id int identity, other int)

insert into three select 1
insert into three select 2

declare @sql nvarchar(max)
set @sql = REPLACE(
'
set identity_insert four on
insert into four (:columns:) select :columns: from three
set identity_insert four off',
':columns:',
stuff((
    select ','+quotename(name)
    from sys.columns
    where object_id=object_id('four')-- and is_identity=0
    for xml path('')),1,1,''))
EXECUTE sp_executesql @SQL

回复收藏 0 原文

~没有更多了~