0

I'm trying to preserve value in identity column in manner to be added later. The value should be used in implicit foreign key when there is no constraint.

For example:

I have this table:

Create table [t](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [value] [varchar](255) Not NULL
)

I want to increment the id without adding line, and get the id, (similar to oracle sequence)

I'm trying to read current sequence value:

Select IDENT_CURRENT('t') as id

result:

id
----
1

trying to increment, use insert with default values:

insert into t default values

result:

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'value', table test.dbo.t; column does not allow nulls. INSERT fails.
The statement has been terminated.

I'm trying to read current sequence value:

Select IDENT_CURRENT('t') as id

result:

id
----
2

Means the sequence increment had been succeeded, even though no row was added.

Now I need a way to read the skipped value in a "scope safe" way.

SCOPE_IDENTITY and @@IDENTITY doesn't been changed unless the insertion was succeeded, and IDENT_CURRENT('t') is not "scope safe".

Does anyone have any idea how can I do so? I prefer a solution that I can use for any table, not limited for a specific table (for specific table: like insert full row and delete it - I need to know the column names).

  • What version of SQL Server? – Scott M Oct 29 '18 at 11:29
  • 3
    If you're trying to simulate an Oracle sequence, why don't you use a sequence in SQL Server? And can you explain why you don't just write the row instead of waiting to add the row later? What could you possibly need to do with an identity value that you haven't assigned yet until you assign it? What problem are you really solving? – Aaron Bertrand Oct 29 '18 at 11:29
  • @AaronBertrand Because I'm trying to preserve id for later insertion, on existing db. can I have access to the identity column sequence? Add a row is table specific solution, I prefer general solution since I have several tables. – SHR Oct 29 '18 at 11:32
  • You have several tables and you want them to share a single set of IDs? Again, why? What problem does this actually solve? And one more time: SQL Server has a sequence feature that sounds like what you need - but it's still unclear what problem this actually solves. – Aaron Bertrand Oct 29 '18 at 11:35
  • @AaronBertrand I'm trying to merge several DBs to a single one, the changes are scanned and all the insertions for each should be added to a merged table. What i'm trying to do is to preserve place in the merged table when foreign key to it been added before the row it pointing to.(when the fk constraint was removed...) – SHR Oct 29 '18 at 11:42
1

You could use DBCC CHECKIDENT (RESEED) for this purpose, but keep in mind you need special permissions, and I'm not sure it worth.

Permissions
Caller must own the schema that contains the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

CREATE TABLE tbl (foo int IDENTITY, bar int);

INSERT INTO tbl (bar) VALUES (10),(15),(20);

SELECT * FROM tbl;

foo         bar
----------- -----------
1           10
2           15
3           20    

/* save current identity value */
DECLARE @CURRENT_ID int;
SELECT @CURRENT_ID = IDENT_CURRENT('tbl');
PRINT 'Current ID = ' + CAST(@CURRENT_ID as varchar(10));

Current ID = 3

/* get next identity value */
DECLARE @NEXT_ID int;
SET @NEXT_ID = @CURRENT_ID + 1;

/* reseed */
DBCC CHECKIDENT (tbl, RESEED, @NEXT_ID);

SELECT * FROM tbl;

foo         bar
----------- -----------
1           10
2           15
3           20
5           30
6           35
7           40


DROP TABLE tbl;
  • The only problem is that it doesn't return the skipped value, so I need to run query to get the value, and it might be changed, therefore it is not safe. insert into t default values is also skip a value, but still, while I can't get the skipped value it doesn't help me. – SHR Oct 29 '18 at 12:04
  • @SHR It's not clear to me how you're saving skipped values. But you can use a variable. – McNets Oct 29 '18 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.