Posted by Robert Klemme on 11/28/06 15:16

On 28.11.2006 00:29, Jim C wrote:
> Is there an equivalent in mssql to SQL Anywhere's GET_IDENTITY which
> reserves the next autoinc value for a table? Yes I know about
> @@Identity and SCOPE_IDENTITY. I need to get the next autoinc value
> _before_ I insert the record due to the way the existing application
> works.
>
> I've seen DBCC CHECKIDENT mentioned but that seems kludgy to me.

You do not post where you need that information. If your insert is in a
stored procedure then SCOPE_IDENTITY after the fact should work.

If for some other reasons you really need the value beforehand, you can
emulate an Oracle sequence

-- test script
create table counter (
cnt int identity(1,1) primary key clustered
)

insert into counter default values
print 'ident: ' + cast(scope_identity() as varchar)
-- optional: truncate table counter

insert into counter default values
print 'ident: ' + cast(scope_identity() as varchar)
-- optional: truncate table counter

drop table counter


The you can pull identity values from that table and use them for the
insert.

Kind regards

robert

[Back to original message]