[RESOLVED] Scope_Identity in SQL Server

[GaryMazzone]

OK this may be an odd question but here goes.


We are looking to pull one table out of our current database (this holds attachments mostly Word Docs) and place it into a supporting database.

All the procedures and views are simple to modify of course just prefix the new database name in front of the table. My question suround the Insert proc. We return the newly inserted identity value use Scope_Identity() function currently, with the move of the table into a new database I don't think that will work (correct?). If I create the proc_Insert in the new DB can I return the Value back to a modified procedure in the orginal DB as an output parameter?

[FunkyDexter]

All scope_identity does is return the last value of an identity column. Provided you've set the seed to an apropriate value the new values should increase from that point and scope_identity will still return the correct value.

When we do this we usually increase the seed to an arbitrarily higher value than the highest existing identity because it makes it easy to spot wich records were inserted after the migration. eg if the seed has reaced 12,345 we'll set the seed to 20,000.

[GaryMazzone]

OK thanks.... So it will pick cross databases that is what I was woundering.