当IDENTITY列存在时，如何从select语句插入表中 [英] How to insert into a table from select statement when IDENTITY column is present

问题描述

我们有一个SQL表'atte）。在每个月的末尾，我们需要将此表中的数据附加到另一个表'attendmastm'中。两个表都有相同的列集。我们使用以下命令来执行此操作：

Hi,

We have an SQL table 'attendmast'. At the end of the every month we need to append the data from this table into another table 'attendmastm'. Both the tables have same set of columns. We are using the following command to do that :

insert into attendmastm select * from attendmast

返回错误

This returns an error

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

我们理解，'select *'不是一个好的编码实践...但在这种情况下，我们不能指定列，因为我们以编程方式收到一个表名，必须在该表上执行此操作。



请建议任何出路。提前谢谢。



我的尝试：



试过谷歌搜索。找到了几个谈论这个的链接。但不完全是我们想要的。

We understand, 'select *' is not a good coding practice... but in this case, We cannot specify the columns since programmatically we receive a table name and this operation has to be done on that table.

Kindly suggest any way out. Thank you in advance.

What I have tried:

Tried googling. Found several links talking about this. Yet not exactly what we want.

推荐答案

如果您希望在将数据从源表复制到目标表时更改键值，那么解决方案1将是你的方式。



但是，既然你描述了你想附加数据，那么我可以想象目标表中的键值应该是相同的如在源表中。如果是这种情况，那么您可以使用SET IDENTITY_INSERT语句来定义键列的值，无论它是否为标识。看看 SET IDENTITY_INSERT（Transact-SQL）| Microsoft Docs [ ^ ]



但如果是这种情况，那么你应该考虑两件事：

- 如果在源表中唯一标识所有数据，目标表为什么包含标识列。

- 每月将数据从另一个表复制到表中听起来像是在试图解决一些问题其他问题，也许是与性能相关的问题。如果是这种情况，那么我认为这可能不是最好的选择。移动数据通常会带来不必要的复杂性，而原始问题或性能也许可以通过其他解决方案解决。

If you want the key value to change when the data is copied from the source table to the target table then the Solution 1 would be the way to go.

However, since you describe that you want to append the data then I could imagine that the key value should be the same in the target table as in the source table. If this is the case then you could use SET IDENTITY_INSERT statement to allow you to define the value of the key column regardless if it's an identity. Have a look at SET IDENTITY_INSERT (Transact-SQL) | Microsoft Docs[^]

But if this is the case then you should consider two things:
- Why the target table contains an identity column if all the data is uniquely identified in the source table.
- Copying data to a table from another table on a monthly basis sounds like you're trying to solve some other problem, perhaps a performance related problem. If this is the case, then I believe that this may not be the best option. Moving the data around introduces often unnecessary complexity while the original problem, performance, could perhaps be solved with other solutions.

由于表始终具有相同的列，因此只列出两者的列：

Since the tables always have the same columns, just list the columns on both:

INSET INTO attendmatsm (col2, col3) SELECT col2, col3 FROM attendmast

由于这些表具有相同的列，因此无论表名是什么，它们都应具有相同的名称。

如果他们不这样做，那么表定义可能会出错，并且你会将不良数据插入主表。



但这确实看起来很糟糕 - 我不确定你要对数据做什么，但你的存储结构可能是错误的。

Since the tables have the same column, they should all have the same name, regardless of the table name.
If they don't, the chances are the table definition will be wrong, and you will be inserting bad data into your master table.

But this does look like a poor design - I'm not sure exactly what you are trying to do with the data, but the chances are your storage structure is wrong.

从你务实得到表名;您也可以使用以下查询获取该表的列名：

Since you are getting table name pragmatically; you can get columns name of that table too using below query:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS where table_schema='dbo' and TABLE_NAME = 'attendmast'

加入逗号分隔字符串中的所有列并用作select语句。



那个可以帮到你。

Join all the columns in comma separated string and use as a select statement.

That can help you.

这篇关于当IDENTITY列存在时，如何从select语句插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！