do multiple inserts for each row

Question

Briefly, my question is how to do the following in t-sql 2012, without cursors (pseudo-code):

for each r in input_list: insert into t1(...) ... if (r.field1 is not null) insert into tA(...) (...@@identity ... r.field1) ... else if (r.field2 is not null) insert into tB(...) (...@@identity... r.field2) ...

Long question:

Suppose I have the following 3 tables, modelling the fact that an object can be either a file or a directory.

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

I need to write a stored proc which takes a list of "logical objects" (which can represent either files or dirs) and must add them to the DB, i.e. it must create, for each logical object, 1) a row in obj, and 2) a row in either file OR dir (depending on whether the logical object represent a file or a directory).

To write this stored proc, I created a table parameter representing the logical object. This must be able to represent both a file and a dir, so it must contain a merge of the (logical) fields of file and dir, as follows:

create type logicalObj as table( dirPath nvarchar(max) null, dirShared bit null, filePath nvarchar(max) null )

My stored procedure is defined with a table-valued parameter as follows:

create procedure foo -- this way the user can pass a list of logical objects to the stored proc @lo logicalObj readonly . as begin ... end

now in the procedure body I think I need to do something like (pseudo-code):

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

My question: how to do this without cursors? It is ok to use features unique to t-sql 2012 (such as sequences) if needed.

Damien_The_Unbeliever · Accepted Answer · 2013-02-13 11:25:27Z

You can use an output clause to capture multiple rows with identity values from the first set-based insert. You can then use ROW_NUMBER() clauses to correlate these captured output values with rows in the original @lo variable.

It will be something like:

declare @IDs table (ID int not null) insert into obj(creation_date) output inserted.id into @IDs select curdate() from @lo --Just makes sure there's one row per row in @lo ;with NumberedIDs as ( select ID,ROW_NUMBER() OVER (ORDER BY ID) as rn from @IDs ), NumberedObjects as ( select *,ROW_NUMBER() OVER (ORDER BY dirPath,filePath) as rn from @lo ) insert into dir (id_obj, path, shared) select nid.ID,no.dirPath,no.dirShared from NumberedObjects no inner join NumberedIDs nid on no.rn = nid.rn where no.dirPath is not null ;with NumberedIDs as ( select ID,ROW_NUMBER() OVER (ORDER BY ID) as rn from @IDs ), NumberedObjects as ( select *,ROW_NUMBER() OVER (ORDER BY dirPath,filePath) as rn from @lo ) insert into file (id_obj, path) select nid.ID,no.filePath from NumberedObjects no inner join NumberedIDs nid on no.rn = nid.rn where no.filePath is not null

It's important to fully query @lo in NumberedObjects in the bottom two inserts and not to filter too early, so that the row numbers keep matching up.

jaybeeuu · Accepted Answer · 2013-02-13 11:17:32Z

Why not just do it as three inserts?

Something like this:

INSERT into obj(creation_date) SELECT curdate() FROM @lo WHERE (lo.dirPath is not null) OR (lo.dirPath is null AND lo.filePath is not null) insert into dir(id_obj, path, shared) SELECT @@identity, lo.dirPath, 1 FROM @lo WHERE lo.dirPath is not null insert into file(id_obj, path) SELECT @@identity, lo.dirPath, 1 FROM @lo lo WHERE lo.dirPath is null AND lo.filePath is not null

I think this won't work, because the first "insert into obj" inserts a bunch of rows, each with their new @@identity. then when you do select @@identity, you are using only the last identity created.

Collectives™ on Stack Overflow

do multiple inserts for each row

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related