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.

Answer 1

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.

Answer 2

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