How to increment (or reserve) IDENTITY value in SQL Server without inserting into table

Question

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?

Answer 1

Use a DBCC CHECKIDENT statement.

Answer 2

Then its no more a one-to-one relationship.

Just break the PK constraint.

Answer 3

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.

Answer 4

This article from SQL Server Books Online discusses the use of the DBCC CHECKIDENT method to update the identity seed of a table.

From that article:

This example forces the current identity value in the jobs table to a value of 30.

USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO

Answer 5

I would look into the OUTPUT INTO feature if you are using SQL Server 2005 or greater. This would allow you to insert into your primary table, and take the IDs assigned at that time to create rows in the secondary table.

I am assuming that there is a foreign key constraint enforced - because that would be the only reason you would need to do this in the first place.

Answer 6

How do you plan on matching them up later? I would not put records into the second table without a record in the first, that is why it is set up in a foreign key relationship - to stio that sort of action. Just why do you not want to insert records into the first table anyway? If we knew more about the type of application and why this is necessary we might be able to guide you to a solution.

Answer 7

this might help

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

http://msdn.microsoft.com/en-us/library/aa259221(SQL.80).aspx

It allows explicit values to be inserted into the identity column of a table.