Is there a way to associate IDENTITY columns from two different tables, so that new records created in either table will have mutually unique values?
That is, a new record in table A will be given value 1, and then a new record created in table B will be given value 2 (because value 1 was already used by the IDENTITY column in table A). Can this be done?
No, identity fields are only unique within the table.I have a table used by multiple applications. One column is an Identify field and is also used as a Primary key. What is\are the best practices to use get the identity value returned after an INSERT made by my code.. I'm worried that if someone does an INSERT into the same table a "zillionth" of a second later than I did, that I could get their Identity value.
TIA,
Barkingdog
Are you using SQL2k5 ? THen you can use the new OUTPUT clause. Otherwise you should have a look on SCOPE_IDENTITY() which should fit your needs. But the new OUTPUT function should be more straight forward in your case.HTH, JEns K. Suessmeyer.
http://www.sqlserver2005.de|||
Jens,
I looked up OUTPUT in sql 2k5 BOL: Here's an example that I found:
>>>
Copy Code
USE AdventureWorks;
GO
DECLARE @.MyTableVar table( ScrapReasonID smallint,
Name varchar(50),
ModifiedDate datetime);
INSERT Production.ScrapReason
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
INTO @.MyTableVar
VALUES (N'Operator error', GETDATE());
--1. Display the result set of the table variable.
SELECT ScrapReasonID, Name, ModifiedDate FROM @.MyTableVar;
--2. Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
GO
>>>>
So with OUTPUT the ScrapReasonID from @.MyTableVar will hold the identity of the row just inserted. Is this correct?
What is the difference in output between queries 1 and 2? Shouldn't they be the same?
TIA,
Barkingdog
