SQL 2 Inserts based on return of first

Question

I am generating a series of Inserts based on data from an Excel file into SQL Server 2014

How do I get the value of the ID of the first INSERT to put into the second

Simplified example where Ontario is a Province of Canada:

Insert into country (Name) values('canada');
Insert into provinces (CountryId, Name) values (???,'ontario');

There are 100 inserts so performance is not an issue.

score 3 · Accepted Answer · answered Nov 25 '15 at 22:42

3

declare @countryid int

Insert into country (Name) values('canada');
SELECT @countryid = SCOPE_IDENTITY()

Insert into provinces (CountryId, Name) values (@countryid,'ontario');

answered Nov 25 '15 at 22:42

tshoemake

1,311
1
17
28

Thanks! If I had to write 100 of these to run all as one big script, do I wrap what you wrote in something? Performance is not a concern. – Ian Vink Nov 25 '15 at 22:46

score 1 · Answer 2 · answered Nov 25 '15 at 22:54

the answer above from tshoemake shows how you can insert one record and get the result. If you want to insert many records in Country and then many records in provinces, you might want to have a look at the OUTPUT clause. You'll have to work out how to join in your list of provinces because this code will just add Ontario to every country:

create table __country
(
    id int identity(1,1) primary key,
    Name varchar(5000)
)


CREATE TABLE __Provinces (
countryid int, 
name varchar(5000)
)

CREATE TABLE #tempIDs
(
    id int
)

INSERT INTO __Country
OUTPUT inserted.id
INTO    #tempIDs
values ('canada'), values('USA')


insert into __Provinces
select  #tempIDs.id, 'ontario'
from    #tempIDs
join    __country
    ON  __country.id = #tempIDs.id


select * from __Provinces

SQL 2 Inserts based on return of first

2 Answers2