Inserting a identity column value into another table

Question

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...

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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 ....)

Answer 5

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.

Answer 6

Here is one simple example of using SCOPE_IDENTITY() to get recent Identity Value http://msdn.microsoft.com/en-us/library/ms190315.aspx