SQL Server 插入多行并递增 int 列
我在一个表中有一些行，需要将它们转移到另一个表。在目标表中，我还需要添加一个具有增量值的字段。
我正在执行以下操作，但我知道插入中的某些内容是错误的，因为递增值 (intCodInterno) 始终相同：
插入 Emp_VISOT.dbo.TBL_GCE_ARTIGOS ( 字符串 , 字符串描述， intCodInterno , intCodTaxaIvaCompra , intCodTaxaIvaVenda , strCod类别 , strAbrevMedStk, strAbrevMedVnd , strAbrevMedCmp , bitAfecta 内部统计 )( 选择A.Artigo， a.描述， IDENT_CURRENT('Emp_VISOT.dbo.TBL_GCE_ARTIGOS')+1, '3', '3', '1' , '嗯', '嗯', '嗯', ‘0’ 来自 PRIVESAM.DBO.Artigo A)
我需要更改什么才能正确增加该值？
谢谢。
编辑：
我对查询做了一个小更改，现在它可以工作了。 我只是在括号内的 IDENT_CURRENT 中插入一个 SELECT：
(SELECT IDENT_CURRENT('Emp_VISOT.dbo.TBL_GCE_ARTIGOS')+1)
我从旧表到新表中获取了所需的所有行，并增加了值。
I have some rows in a table and need to transfer them to another table. In the destination table i need also to add a field with an incremental value.
I'm doing the following, but i know that something in the insert is wrong, because the incremented value (intCodInterno) is always the same:
INSERT INTO Emp_VISOT.dbo.TBL_GCE_ARTIGOS ( strCodigo , strDescricao , intCodInterno , intCodTaxaIvaCompra , intCodTaxaIvaVenda , strCodCategoria , strAbrevMedStk , strAbrevMedVnd , strAbrevMedCmp , bitAfectaIntrastat )( SELECT A.Artigo , a.Descricao , IDENT_CURRENT('Emp_VISOT.dbo.TBL_GCE_ARTIGOS')+1, '3' , '3' , '1' , 'Un' , 'Un' , 'Un' , '0' FROM PRIVESAM.DBO.Artigo A)
What do i need to change so the value is incremented correcty?
Thank you.
EDIT:
I made a small change in the query, and now it works.
I just insert a SELECT in the IDENT_CURRENT inside brackets:
(SELECT IDENT_CURRENT('Emp_VISOT.dbo.TBL_GCE_ARTIGOS')+1)
I got all the rows that i need from the old table to the new with the incremented value.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
IDENT_CURRENT('Emp_VISOT.dbo.TBL_GCE_ARTIGOS')+1当您想要运行查询时评估一次，并且所有行将获得相同的 id。
第一个解决方案是通过循环结构（如游标或任何其他结构）迭代选择结果并插入递增索引（您这样做）
第二个解决方案是在目标表中将该列设置为
identitythe
IDENT_CURRENT('Emp_VISOT.dbo.TBL_GCE_ARTIGOS')+1evaluated once when you want to run the query and all the rows will get the same id.
first solution is to iterate over the select result by a loop construct like cursor or whatsoever and insert the incremented index(you do that)
second solution is to make that column in destination table
identity使用 intCodInterno 删除部分，并在 SQL Server 中使用 Identity 属性 自动为你增加它。
Remove the part with intCodInterno and in SQL Server use the Identity property to automatically increment it for you.
IDENT_CURRENT 在事务提交之前不会更新，因此它的值在插入之前保持不变。
以下是解决此问题的三个选项：
使用某种计数器 (@newRowNum)，这样对于 SELECT 查询中的每一行，@newRowNum = @newRowNum +1，因此您的 intCodInterno 编号 = IDENT_CURRENT() + @新行号。但这可能需要大量的黑客工作才能实现。不建议这样做。
使用您现在拥有的相同业务逻辑顺序插入行 - 然而，它的性能会大大降低。不推荐它。
将目标表中的该列设置为标识列本身。这是迄今为止最好的方法。
如果您需要一个自定义标识函数（我假设您现在不使用标识列是有原因的），您可以使用上面概述的一些步骤创建一个： http://www.sqlteam.com/article/custom-auto- generated-sequences-with-sql-server
IDENT_CURRENT won't update until the transaction commits, therefore its value remains constant until you insert.
Here are three options for fixing this issue:
Use some kind of counter (@newRowNum) such that for each row in your SELECT query, @newRowNum = @newRowNum +1, and thus your intCodInterno number = IDENT_CURRENT() + @newRowNum. This would probably require a lot of hacking to work though. Don't recommend it.
Insert your rows sequentially using the same business logic you have now - it will be tremendously less performant, however. Don't recommend it.
Set that column in your destination table to be an identity column itself. This is by far the best way to do it.
If you need a custom identity function (I assume there's a reason you're not using an identity column now), you can create one using some of the steps outlined above: http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server
就我而言，我使用相同的业务逻辑顺序插入行。我无法使用自动增量，因为我必须将旧数据也导入到此列中。导入数据后，您可以更新自动增量列。
In my case , i Inserted rows sequentially using the same business logic. I cannot use auto increment as i have to import old data also into this column. Once you have imported the data then u may go for updating the column for auto increment .