Recursively adding data from scratch

Question

Suppose I have a table that looks like this (using C# for example):

public class Table {
    public Guid Id {get; set;}
    public string Name {get; set;}
    public Guid ParentId {get; set;}
}

This is a recursive table where a row can have a linked parent in the same table.

Assuming I am starting from scratch (i.e. there is no data to select from originally from other tables), what is the best way to insert new rows of up to depth X via TSQL?

Suppose I want the data to look something like this:

The purpose of this script is for load testing - thus I don't have any data I can select from. Data is continually going to be added, deleted, and added again. Most of what I've found are either SELECT CTE's or INSERT after SELECT.

I'm sure an alternative is to write a console app to do this for me (admittedly my SQL game is not that great), but I'd like to see if there's a way to do this in SQL.

Answer 1

There is nothing special (necessarily) about this data. You can script inserts as a sql insert statement. For example, using int instead of guid ID values for simplicity here:

create table Scratch (
    id int,
    [name] nvarchar(100),
    parentId int, 
    primary key (id))

alter table Scratch add constraint fk_temp_id 
    foreign key (parentId) references Scratch(id)

insert into Scratch (id, [Name], ParentId)
values 
    (1, 'a', null),
    (2, 'b', 1),
    (3, 'c', 1),
    (4, 'd', 2),
    (5, 'e', 2),
    (6, 'f', 3),
    (7, 'g', 3)

That being said, here is how you could create the data with a loop up to N levels deep - but more than 63 levels will overflow the bigint sequence values (and this could start to take a bit of time as number of levels increases - just 20 levels will be a million rows).

create table dbo.Scratch (
    id uniqueidentifier,
    [name] nvarchar(100),
    parentId uniqueidentifier, 
    seq int,
    parentSeq int,
    primary key (id));

alter table dbo.Scratch add constraint fk_temp_id 
    foreign key (parentId) references Scratch(id);

-- seed first row
insert into dbo.Scratch (id, [name], parentId, seq, parentSeq) values (newid(), '1', null, 1, null);

-- build binary tree with int ids
declare @I int = 0;
declare @J int = 0;
declare @K int;
declare @LEVELS int = 3; --> To be set as needed, using zero-based counting
declare @next_node bigint = 2;
while @I < @LEVELS-1
begin
    set @I += 1;
    set @J = 0;
    set @K = cast(power(2, @I) as bigint)
    while @J < @K / 2
    begin
        set @J += 1
        insert into dbo.Scratch(id, [name], ParentId, seq, parentSeq)
            values 
                (newid(), cast(@next_node as nvarchar(100)), null, @next_node, @next_node / 2),
                (newid(), cast(@next_node+1 as nvarchar(100)), null, @next_node+1, @next_node / 2);
        set @next_node += 2;
    end
end     

-- update the parent guids
update b
set b.parentId = a.id
from dbo.Scratch a
inner join dbo.Scratch b
on a.seq = b.ParentSeq

-- drop the temporary columns needed for creating the data
--alter table dbo.Scratch drop column parentSeq;
--alter table dbo.Scratch drop column seq;

--select * from dbo.scratch order by seq;

Result:

id	name	parentId	seq	parentSeq
46b16f14-20b9-4388-a7f2-832e2c56215d	1		1
a613b692-138d-4207-8e84-43cccd0d2476	2	46b16f14-20b9-4388-a7f2-832e2c56215d	2	1
0ff8b67a-63fe-4125-b2b9-bb962d67f526	3	46b16f14-20b9-4388-a7f2-832e2c56215d	3	1
f27f82b6-d1c1-4cb2-810d-4fa21ffef5bc	4	a613b692-138d-4207-8e84-43cccd0d2476	4	2
346fd62a-6727-4414-975b-2b1659ee40c9	5	a613b692-138d-4207-8e84-43cccd0d2476	5	2
a8d3928a-6357-47ef-b1d0-0be76fa9233f	6	0ff8b67a-63fe-4125-b2b9-bb962d67f526	6	3
1c669bdf-63e1-44af-858b-752003d9a980	7	0ff8b67a-63fe-4125-b2b9-bb962d67f526	7	3
6e7ac3b1-921e-4ddc-a0c7-ad9499992689	8	f27f82b6-d1c1-4cb2-810d-4fa21ffef5bc	8	4
6786d550-7df8-4ecd-b7ca-6b7658b91b68	9	f27f82b6-d1c1-4cb2-810d-4fa21ffef5bc	9	4
d3113177-cc37-4d55-8b2d-b603345837a8	10	346fd62a-6727-4414-975b-2b1659ee40c9	10	5
bb32c1c2-9f75-455e-9aa1-fc38ddd25a6c	11	346fd62a-6727-4414-975b-2b1659ee40c9	11	5
1a218245-b6d4-41a7-8117-1b3d0bd2fdd9	12	a8d3928a-6357-47ef-b1d0-0be76fa9233f	12	6
fe8f1882-a89a-4201-83f4-b72e528ba9da	13	a8d3928a-6357-47ef-b1d0-0be76fa9233f	13	6
edab3434-c038-406e-bcf0-b6139feff6c0	14	1c669bdf-63e1-44af-858b-752003d9a980	14	7
19007b10-e86f-4fe4-9c0f-8e739f4a5c89	15	1c669bdf-63e1-44af-858b-752003d9a980	15	7