Batch insert data in table that has a parent child relationship

Question

I am working on a function that would insert a data in a table that has a parent child relationship. My current implementation is to iterate the objects to insert them one by one. I am thinking of batch insert to lessen the database round trip using SQL OR C# OR PetaPoco. Any one has an idea?

PersonID    ParentID    LastName    FirstName
1            (null)       John        Doe
2              1          Mary        Doe
3              1          Ken         Doe
4              2          Jane        Doe

http://sqlfiddle.com/#!7/5edc6/6

https://stackoverflow.com/questions/1006969/why-are-batch-inserts-updates-faster-how-do-batch-updates-work — Arslan Ali, Aug 11 '18 at 01:31
maybe you can use `Stored Procedure` to perform the insert. Pass in the parent plus all the childs to the SP as `Table Valued Parameters`. So you call the SP once for each (parent + child) — Squirrel, Aug 11 '18 at 03:01
Squirrel is right, TVP is the way. You do not even need SP - you never really need SP, as it adds no new fuctionality. Just execute something like `insert into dbo.SomeTable select * from @tableParameter` with the right table parameter. — Antonín Lejsek, Aug 11 '18 at 03:26
@AntonínLejsek, i think you will required SP as there is a parent-child relationship on the same table. It need to insert parent row get the ID for child row — Squirrel, Aug 11 '18 at 03:44
Well, since the PersonId is not an identity column, it's quite simple - all you need to do is use a table valued parameter. Plenty of examples on line and in SO. — Zohar Peled, Aug 11 '18 at 06:29
At some point you know the relation between the two entities, you could just disable constraints (and if you have identiy column, use SET IDENTITY_INSERT) and insert data manually (including link between perent/child) ... Just make sure to add parent before any children. — Minus, Aug 13 '18 at 15:25

score 0 · Answer 1 · answered Aug 11 '18 at 01:44

0

You could collect the data when you iterate over it and do one INSERT with multiple rows.

INSERT INTO Persons
            (PersonID,
             ParentID,
             Lastname,
             Firstname)
            VALUES (1,
                    null,
                    'John',
                    'Doe'),
                   (2,
                    1,
                    'Mary',
                    'Doe'),
                   ...;

answered Aug 11 '18 at 01:44

sticky bit

36,626
12
31
42

your code is working if the personId is existing, the data to be inserted is all new and personid is null. The above data shows the sample structure of the table only. – cris gomez Aug 11 '18 at 02:22
and the other thing is that, there is a hierarchy on data, so their is a dependency on the record that was inserted earlier. – cris gomez Aug 11 '18 at 02:48
1

@crisgomez the whole table is inserted at once, so dependency is not an issue. But it is not a good idea to send data as a huge text, the parsing overhead would be bad. – Antonín Lejsek Aug 11 '18 at 03:11

Esperento57 · Answer 2 · 2018-08-11T06:15:03.447

Create a working table with same structure (named TableB for example)
Insert your datas into TableB (in c# you can use datatable and SqlBulkCopy for best performance, look here)
Insert into your final table your datas with the working table but with column parent to null like this :

insert into tableA (PersonID, LastName, FirstName) select PersonID , LastName, FirstName from tableA;
Update parent id with the working table

update f1 set f1.parentid=f2.parentID from tableA f1 inner join tableB f2 on f1.PersonID=f2.PersonID;

Note1 : 3. and 4. should be in the same transaction if you want rollback if error

Note2 : In the gived example by Microsoft for SqlBulkCopy, they build the structure of datatable, but you can exec 'select top 0 * from tableA' with dataset object and get the datatable for this

Note3 : You could insert directly into the final table with the sqlbulkcopy method since this method disables the constraints it seems to me, but it's really dangerous... The constraints are there for a reason.

Batch insert data in table that has a parent child relationship

2 Answers2