sql - do multiple inserts for each row -

sql - do multiple inserts for each row -

briefly, question how next in t-sql 2012, without cursors (pseudo-code):

for each r in input_list: insert t1(...) ... if (r.field1 not null) insert ta(...) (...@@identity ... r.field1) ... else if (r.field2 not null) insert tb(...) (...@@identity... r.field2) ...

long question:

suppose have next 3 tables, modelling fact object can either file or directory.

obj(id int, creation_date datetime) -- objects have creation date. file(id int, id_obj int, path nvarchar(max)) -- id_obj foreign key obj dir(id int, id_obj int, path nvarchar(max), shared bit) -- id_obj foreign key obj

i need write stored proc takes list of "logical objects" (which can represent either files or dirs) , must add together them db, i.e. must create, each logical object, 1) row in obj, , 2) row in either file or dir (depending on whether logical object represent file or directory).

to write stored proc, created table parameter representing logical object. must able represent both file , dir, must contain merge of (logical) fields of file , dir, follows:

create type logicalobj table( dirpath nvarchar(max) null, dirshared bit null, filepath nvarchar(max) null )

my stored procedure defined table-valued parameter follows:

create procedure foo -- way user can pass list of logical objects stored proc @lo logicalobj readonly . begin ... end

now in procedure body think need (pseudo-code):

for each lo in @lo: insert obj(creation_date) values (curdate()) if lo.dirpath not null insert dir(id_obj, path, shared) values (@@identity, lo.dirpath, 1 ) else if lo.filepath not null insert file(id_obj, path) values (@@identity, lo.dirpath )

my question: how without cursors? ok utilize features unique t-sql 2012 (such sequences) if needed.

you can utilize output clause capture multiple rows identity values first set-based insert. can utilize row_number() clauses correlate these captured output values rows in original @lo variable.

it like:

declare @ids table (id int not null) insert obj(creation_date) output inserted.id @ids select curdate() @lo --just makes sure there's 1 row per row in @lo ;with numberedids ( select id,row_number() on (order id) rn @ids ), numberedobjects ( select *,row_number() on (order dirpath,filepath) rn @lo ) insert dir (id_obj, path, shared) select nid.id,no.dirpath,no.dirshared numberedobjects no inner bring together numberedids nid on no.rn = nid.rn no.dirpath not null ;with numberedids ( select id,row_number() on (order id) rn @ids ), numberedobjects ( select *,row_number() on (order dirpath,filepath) rn @lo ) insert file (id_obj, path) select nid.id,no.filepath numberedobjects no inner bring together numberedids nid on no.rn = nid.rn no.filepath not null

it's of import query @lo in numberedobjects in bottom 2 inserts , not filter early, row numbers maintain matching up.

sql tsql insert