T-SQL Increment Id after Insert

Question

I'm currently working on a stored procedure in SQL Server 2012 using T-SQL. My problem: I have several SWOTs (e.g. for a specific client) holding several SWOTParts (strengths, weaknesses, opportunities, and threats). I store the values in a table Swot as well as in another table SwotPart.

My foreign Key link is SwotId in SwotPart, thus 1 Swot can hold N SwotParts. Hence, I store the SwotId in every SwotPart.

I can have many Swots and now need to set the SwotId correctly to create the foreign key. I set the SwotId using SCOPE_IDENTITY() unfortunately it only takes the last SwotId from the DB.
I'm looking for something like a for loop to increment the SwotId after each insert on the 1st insert.

DECLARE @SwotId INT = 1;    

-- 1st insert
SET NOCOUNT ON
INSERT INTO [MySchema].[SWOT]([SwotTypeId]) // Type can be e.g. a sepcific client
SELECT SwotTypeId
FROM @SWOTS

SET @SwotId = SCOPE_IDENTITY(); // currently e.g. 7, but should increment: 1, 2, 3...

-- 2nd insert
SET NOCOUNT ON
INSERT INTO [MySchema].[SwotPart]([SwotId], [FieldTypeId], [Label]) // FieldType can be e.g. Streangh
SELECT @SwotId, FieldTypeId, Label
FROM @SWOTPARTS

Do you know how to solve this issue? What could I use instead of SCOPE_IDENTITY()?

Thank you very much!

When you want to insert multiple `@swots` and `@swotparts` how do you know which `@swotparts` goes to which `@swots` ? — SqlZim, Apr 20 '17 at 12:39
I can't see your insert query for `Swot` table, whose id will be used as foreign key in `SwotPart` table. you can't put arbitrary value in a Foreign key column. correct me if i am wrong. — A_Sk, Apr 20 '17 at 12:46
http://stackoverflow.com/questions/34826450/fastest-way-to-perform-nested-bulk-inserts-with-scope-identity-usage/34832231#34832231 — etsa, Apr 20 '17 at 12:57

score 3 · Accepted Answer · answered Apr 20 '17 at 12:47

You can output the inserted rows into a temporary table, then join your @swotparts to the temporary table based on the natural key (whatever unique column set ties them together beyond the SwotId). This would solve the problem with resorting to loops or cursors, while also overcoming the obstacle of doing a single swot at a time.

set nocount, xact_abort on;

create table #swot (SwotId int, SwotTypeId int);

insert into MySchema.swot (SwotTypeId) 
  output inserted.SwotId, inserted.SwotTypeId into #swot
select SwotTypeId
from @swots;

insert into MySchema.SwotPart(SwotId, FieldTypeId, Label)
select s.SwotId, p.FieldTypeId, p.Label
from @swotparts p
  inner join #swot s
    on p.SwotTypeId = p.SwotTypeId;

Thanks, works like a charm! The solution with the temporary table was what I was kind of looking for to store the Id — TimHorton, Apr 20 '17 at 13:01

Cosmin · Answer 2 · 2017-04-20T12:46:42.130

Unfortunately I cant comment so I`ll leave you an answer hopefully to clarify some things:

Since you need to create the correct foreign key I don`t understand why do you need to increment a value instead of using the id inserted into the SWOT table.

I suggest returning the inserted id using the SCOPE_IDENTITY right after the insert statement and use it for you insert into the swot parts (there is plenty of info about it and how to use it)

 DECLARE @SwotId INT;    
-- 1st insert
INSERT INTO [MySchema].[SWOT]([SwotTypeId]) // Type can be e.g. a sepcific client
SET @SwotId = SCOPE_IDENTITY();

-- 2nd insert
INSERT INTO [MySchema].[SwotPart]([SwotId], [FieldTypeId], [Label])
SELECT @SwotId, FieldTypeId, Label
FROM @SWOTPARTS

T-SQL Increment Id after Insert

2 Answers2