Inserting nested entities with autoincrement using SQL Server

Question

Suppose we have class with property of the same type (I will describe classes in C#, but this doesn't matter here)

class Exception
{
    public string Message { get; set; }

    public string StackTrace { get; set; }

    public Exception InnerException { get; set; }
}

As you can see from the code above there is exception class with nested exception.

Let's create table for storing these exceptions

CREATE TABLE Exceptions
(
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Message] NVARCHAR(MAX) NOT NULL,
    StackTrace NVARCHAR(MAX) NULL,
    InnerExceptionId INT NULL,
    CONSTRAINT FK__Exceptions_Id__Exceptions_InnerExceptionId
        FOREIGN KEY(InnerExceptionId) REFERENCES Exceptions (Id)
);

Of course I can create some code that just works: for example some stored procedures that takes TVP as argument and then loop though rows of TVP and inserts new rows one by one.

But are there any SQL code that can elegantly insert exceptions with nesting?

Answer 1

• Put your rows in some list/collection, assign surrogate id/rowno to each row, build relations between rows based on that id/rowno; you can start from 1 each time - it does not matter
• Push these data to db
• Insert all rows into persistent Exceptions table, obtain generated IDENTITY values linked to original surrogate id
• Convert links based on surrogate id into IDENTITY-based links, update target table

So, @original_list is supposed to be an input argument. Then you need a trick that MERGE does well - linking src.id to dst.id after insert. Then just convert old values into new. All commands are set-based, no loops.

DECLARE @original_list TABLE (
  surrogate_no int not null,
  msg varchar(100) not null,
  inner_surrogate_no int null
);

insert into @original_list (surrogate_no, msg, inner_surrogate_no)
values
  (1000, 'err 1000', null),
  (1010, 'err 1010->1000', 1000),
  (1020, 'err 1020', null),
  (1030, 'err 1030->1010', 1010)

-- args prepared, starting migration

DECLARE @migration TABLE (
  src_id int not null,
  dst_id int not null
)

merge Exceptions t
using @original_list s
on 1=0 --<< we are not looking for updates
when not matched by target then
  insert (message) 
  values (s.msg)
output s.surrogate_no, inserted.id ---<<< here is the main trick: src.id and matching dst.id
into @migration(src_id, dst_id)
;

-- now all error messages are inserted, but none of them have InnerExceptionId

update e set
  InnerExceptionId = mp.dst_id
from Exceptions e
inner join @migration m  --<< get original surrogate_no 
  on m.dst_id = e.id
inner join @original_list o --<< grab original row
  on o.surrogate_no = m.src_id
inner join @migration mp  --<< locate dst.id for inner_surrogate_no
  on mp.src_id = o.inner_surrogate_no

This is a common solution for such tasks. Full source

Final data:

| Id |        Message | StackTrace | InnerExceptionId |
|----|----------------|------------|------------------|
|  1 |       err 1000 |     (null) |           (null) |
|  2 | err 1010->1000 |     (null) |                1 |
|  3 |       err 1020 |     (null) |           (null) |
|  4 | err 1030->1010 |     (null) |                2 |

Treeview by recursive cte:

|                                        Message | Lvl | id | InnerExceptionID |
|------------------------------------------------|-----|----|------------------|
|                                       err 1000 |   1 |  1 |           (null) |
|                                       err 1020 |   1 |  3 |           (null) |
|                    err 1010->1000>>>(err 1000) |   2 |  2 |                1 |
| err 1030->1010>>>(err 1010->1000>>>(err 1000)) |   3 |  4 |                2 |

Note, sqlfiddle did not allow me to run MERGE inside bigger script (it was failing all the time with semicolon exception), so I turned @ tables into persistent tables and put merge into dynamic sql, but you don't need to do that on real server.

Answer 2

Some remarks:

• ExceptionTableType has no 'order' field, as such, when you load the information you presume that the order you read the records in is going to be the order they were created. This is by no means guaranteed and might actually do strange things to your results!
• The InnerExceptoinId column feels 'backward', it seems to mean 'When filled in, this record is the InnerException from the referenced record', while I somehow would expect that it's use should work the other way around: "When filled in, the referenced record is an InnerException of mine". Right now you need to scan the entire table to see if there is an InnerException for a given record, and then repeat over and over until nothing is found anymore.

Anyway, staying close to your solution I've created a set-based version. Not sure it's going to matter much performance-wise, but out of principle it's to be preferred over the cursor-based approach =)

CREATE PROCEDURE LogException2
    @exceptions ExceptionTableType READONLY
AS

    DECLARE @rowcount int

    SELECT source_id = IDENTITY(int, 1, 1),
           insert_id = Convert(int, 0),
           Message,
           StackTrace
      INTO #loading
      FROM @exceptions
    -- really should have ORDER BY here!

    SELECT source_id = source_id + 0,
           insert_id
      INTO #feedback
      FROM #loading
    WHERE 1 = 2

    INSERT INTO Exceptions ([Message], StackTrace, InnerExceptionId)
    OUTPUT l.source_id, [inserted].Id INTO #feedback (source_id, insert_id)
    SELECT l.Message,
           l.StackTrace,
           NULL
      FROM #loading

    UPDATE upd
       SET InnerExceptionID = f.insert_id + 1
      FROM Exceptions upd
      JOIN #feedback f
        ON f.insert_id = upd.Id
       AND f.source_id <> 1 -- first row has no InnerExpectionId

    Return(0)

GO