sql server - t-sql 2012 update foreign key value in primary table -

in special request run, need update locker , lock tables in sql server 2012 database, have following 2 table definitiions:

create table [dbo].[locker]( [lockerid] [int] identity(1,1) not null, [schoolid] [int] not null, [number] [varchar](10) not null,     [lockid] [int] null   constraint [pk_locker] primary key nonclustered  ( [lockerid] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key =   off,      allow_row_locks = on, allow_page_locks = on, fillfactor = 97)  on    [primary]  ) on [primary]   create table [dbo].[lock]( [lockid] [int] identity(1,1) not null, [schoolid] [int] not null, [comboseq] [tinyint] not null  constraint [pk_lock] primary key nonclustered  ( [lockid] asc ) 

the locker table main table , lock table secondary table. need add 500 new locker numbers user has given me place in locker table , uniquely defined lockerid. need add 500 new rows correspsonding lock table uniquely defined in lock table , identified lockid.

since lockid key value in lock table , uniquely defined in locker table, know how update lock table 500 new rows. take value of lockid (from lock table 500 new rows created) , uniquely place 500 lockids uniquely 500 rows created lock table.

i have sql looks following far:

 declare @schoolid int = 999   insert test.dbo.locker ( [schoolid], [number])      select distinct lkr.schoolid, a.lockernumber      [inputtable]     join test.dbo.school sch        on a.schoolnumber = sch.type       , a.schoolnumber =     @schoolnumber     join test.dbo.locker lkr        on sch.schoolid = lkr.schoolid       , a.lockernumber not in (select number                                  dbo.locker                                  schoolid = @schoolid)     order lkr.schoolid,  a.lockernumber  

i not how complete rest of task of placing lockerid uniquely lock , locker tables? can either modify sql listed above and/or come new sql show me how accomplish goal?

you should use output statement. first should add rows lock table gram lockid , prepare insert locker table. shoul meet expectations:

declare @tmp table (lockid int)  insert dbo.lock         ( schoolid, comboseq )     output inserted.lockid @tmp ( lockid ) (select     999,     1    master..spt_values sv sv.type = 'p' , sv.number <= 500);  insert dbo.locker( schoolid, number, lockid )  select x.schoolid, x.lockernumber, y.lockid (     select top 100 percent disctinct lkr.schoolid, a.lockernumber, row_number() on (order a.lockernumber) rn     [inputtable]     join test.dbo.school sch on a.schoolnumber = sch.type      , a.schoolnumber = @schoolnumber     join test.dbo.locker lkr on sch.schoolid = lkr.schoolid      , a.lockernumber not in (select number dbo.locker        schoolid = @schoolid)     order lkr.schoolid,  a.lockernumber ) x     join (select t.lockid, row_number() on (order t.lockid) rn @tmp t ) y      on x.rn = y.rn