Reputation: 8568
Good Morning. I have two tables, and one references the other. When I insert into the primary table, the primary key is auto-generated, viz Identity field. I need to insert this value into the second table.
I found out using the OUTPUT clause will give me the just inserted identity value, ans so I tried this.
insert into owners (pId)
insert into personal (firstName)
output inserted.pId
values ('fn')
It doesn't work though. I get an error:
Incorrect syntax near the keyword 'insert'
The personal table is the primary table, and the owners table contains the foreign key. How can I do the required in SQL Server?
I've got stuck-up here for the past two days...
Upvotes: 1
Views: 15515
Reputation: 3418
Here is one simple example of using SCOPE_IDENTITY() to get recent Identity Value http://msdn.microsoft.com/en-us/library/ms190315.aspx
Upvotes: 0
Reputation: 755411
I think you just have your syntax slightly off - you can definitely take values inserted into the main table and use the OUTPUT
clause to insert those into a secondary table.
INSERT INTO dbo.personal(firstName)
OUTPUT INSERTED.pId INTO dbo.owners(pId)
VALUES('fn')
This will insert a new row into personal
and set the column firstName
to fn
. From that insert, the inserted row's identity column pId
is then inserted into the other table, owners
, as that table's pId
column.
See the MSDN documentation on the OUTPUT clause for more details - you can either output any of the inserted values to the console (e.g. SQL Server Mgmt Studio), or you can output those values into a temporary or a permanent table.
Update: as 'dradu' has pointed out - this approach won't work in your case here, since the column in the owners
table is part of a FK constraint (I had missed that point from your question). So you'll need to use some other way to do this - probably outputting the necessary information into a temporary table / table variable in your code
Upvotes: 4
Reputation: 2677
Try the following steps
1) Apply transaction level on insertion
2) Get last inserted id using Scope_Identity() function.
When you apply transaction level it will lock your tables and other/same user cannot insert the value in this time.
try this it will work for you.
Upvotes: 3
Reputation: 2782
Since OUTPUT
clause cannot be used directly because of the foreign key, you could add the generated IDs into a temporary table, then insert those values into the owners
table:
BEGIN TRANSACTION
CREATE TABLE #ids(ID INT)
INSERT INTO personal(firstName)
OUTPUT inserted.pid INTO #ids
SELECT 'A'
UNION SELECT 'B'
INSERT INTO owners(pid)
SELECT ID FROM #ids
COMMIT TRANSACTION
SCOPE_IDENTITY
will work too, but it's limited to one value.
Upvotes: 3
Reputation: 35582
I think Your option is to use SCOPE_IDENTITY()
but the other closest to your option is IDENT_CURRENT(‘tablename’)
so I thought, I post detail of detail of other identity options as well which might help you to understand your choice and might helpful some other time
@@IDENTITY It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
SCOPE_IDENTITY() It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
IDENT_CURRENT(‘tablename’) It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
Upvotes: 2
Reputation: 16812
You can use the SCOPE_IDENTITY() function to return the identity value inserted.
DECLARE @id INT
INSERT INTO [Personal] (Colums ....) VALUES (this, that, stuff)
SET @id = SCOPE_IDENTITY()
INSERT INTO [Owners] (Colums ....) VALUES (@id ....)
Upvotes: 2