如何在SQL Server中增加（或保留）IDENTITY值而不插入到表中 [英] How to increment (or reserve) IDENTITY value in SQL Server without inserting into table

问题描述

有没有办法保留或跳过或增加身份列的价值？

我有两张桌子加入一对一的关系。第一个有IDENTITY PK列，第二个int PK（不是IDENTITY）。我曾经插入第一，获得ID和插入第二。它可以正常工作。

现在我需要在第二个表中插入值，而不是先插入。

现在，如何增加IDENTITY种子，所以我可以将它插入到第二个表中，但是在第一个表的ID中留下孔？

编辑：更多信息

$ b

   - 我需要新的种子数，但不是表行
  - 所以我会插入foo行，获取id，并删除它
 INSERT INTO TABLE1（SomeRequiredField）VALUES（'foo'）; 
 SET @NewID = SCOPE_IDENTITY（）; 
 DELETE FROM TABLE1 WHERE ID = @ NewID; 
 
  - 然后我可以在TABLE2中插入
 INSERT INTO（ID，Field，Field）VALUES（@NewID，'Value'，'Value'）; 
  

再一次 - 这是有效的。

问题是我可以在不插入表的情况下获得ID吗？

DBCC需要所有者权限;有没有一个干净的用户可调用的SQL来做到这一点？

解决方案

这种情况将使您的整体数据结构非常难以理解。如果这些价值之间没有关系，那就打破这种关系。

有些方法可以解决这个问题，可以做你正在寻找的东西，但是通常它是在分布式环境中，而不是由于似乎是一个数据模型改变。

Is there a way to reserve or skip or increment value of identity column?

I Have two tables joined in one-to-one relation ship. First one has IDENTITY PK column, and second one int PK (not IDENTITY). I used to insert in first, get ID and insert in second. And it works ok.

Now I need to insert values in second table without inserting into first.

Now, how to increment IDENTITY seed, so I can insert it into second table, but leave "hole" in ID's of first table?

EDIT: More info

This works:

-- I need new seed number, but not table row 
-- so i will insert foo row, get id, and delete it
INSERT INTO TABLE1 (SomeRequiredField) VALUES ('foo'); 
SET @NewID = SCOPE_IDENTITY(); 
DELETE FROM TABLE1 WHERE ID=@NewID;

-- Then I can insert in TABLE2  
INSERT INTO (ID, Field, Field) VALUES (@NewID, 'Value', 'Value'); 

Once again - this works.

Question is can I get ID without inserting into table?

DBCC needs owner rights; is there a clean user callable SQL to do that?

解决方案

This situation will make your overall data structure very hard to understand. If there is not a relationship between the values, then break the relationship.

There are ways to get around this to do what you are looking for, but typically it is in a distributed environment and not done because of what appears to be a data model change.

这篇关于如何在SQL Server中增加（或保留）IDENTITY值而不插入到表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！