Inserting multiple rows at once when parent-child relationship is in the same table

Question

I'm trying to use bulkinsert to insert multip rows at once.

In the data to be inserted, the Parent-Child relationship is determined by the "ParentName" column, a nvarchar type.

But in the data table, the parent-child relationship is determined by ParentId, a uniqueidentifier.

How should I approach this so while inserting, the parentId is the generated id of the child's parent record?

Please let me know if I can clarify my questions further. I'm using sql server 2005.

I would go after it with output an clause. msdn.microsoft.com/en-us/library/ms177564(v=sql.120).aspx First insert all the parents and output the parentname and parentid. Stage the children to a #temp and use the data in the output table to insert the children. — paparazzo
– paparazzo, Commented Jan 24, 2014 at 20:30
@Blam, thanks for the idea. I just looked up output clause. that may be doable. I'm trying something with it now. — Laguna
– Laguna, Commented Jan 24, 2014 at 20:39
It is doable but it is some work. But I don't know another way. Why are you on 2015? I would take free SQL 2012 Express over 2015. — paparazzo
– paparazzo, Commented Jan 25, 2014 at 1:53
The Output clause is definitely the way to go; this is what it's built for. You could do something slow and ugly with a cursor and a sorted I out set which looked up the parent by nvarchar name I a sub query, but that would be slow and wouldn't smell so good. :-) — jbeldock
– jbeldock, Commented Jan 25, 2014 at 20:53
@Blam, I don't have a choice over the sql svr version. It's up to the infrastructure group of the company that I work for. just curious, do the later versions support this more easily? oh and btw, the output clause really did it for me! it's working and it's blazing fast in inserting thousands of records! — Laguna
– Laguna, Commented Jan 27, 2014 at 15:08

paparazzo · Accepted Answer · 2014-01-26 15:02:23Z

Added my comment as an answer in case that did it for you

I would go after it with an output an clause. TSQL Output First insert all the parents and output the parentname and parentid. Stage the children to a #temp and use the data in the output table to insert the children.

Collectives™ on Stack Overflow

Inserting multiple rows at once when parent-child relationship is in the same table

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related