Insert into a table recursively

[Robert Ludig]

I have a Table that contains Items of the Type "Step". The primary key
is "StepID". Each step can have have a target step, wich represents a
subsequent step. So I have a Foreign key relationship within the same
table:

Primary Key: StepID --> Foreign Key: TargetStepID

Now if I want to insert a group of new Steps into the table, I can only
insert steps whose target step is already insterted to the table. How
would I solve this problem ? Do I need to write my own client side (I
am using ADO.NET / C#) that loops through the targetsteps and inserts
the targetsteps first ? Or is there a more celver way to do this ?

[Erland Sommarskog]

Robert Ludig (sc******************@gmx.de) writes:

I have a Table that contains Items of the Type "Step". The primary key
is "StepID". Each step can have have a target step, wich represents a
subsequent step. So I have a Foreign key relationship within the same
table:

Primary Key: StepID --> Foreign Key: TargetStepID

Now if I want to insert a group of new Steps into the table, I can only
insert steps whose target step is already insterted to the table. How
would I solve this problem ? Do I need to write my own client side (I
am using ADO.NET / C#) that loops through the targetsteps and inserts
the targetsteps first ? Or is there a more celver way to do this ?

The easiest is of course to insert all at once:

CREATE TABLE rekursiv (a int NOT NULL PRIMARY KEY,
b int NULL REFERENCES rekursiv(a))
go
INSERT rekursiv(a, b)
SELECT 1, NULL
UNION
SELECT 10, 1
UNION
SELECT 20, 10
go
SELECT * FROM rekursiv
go
DROP TABLE rekursiv

If the StepID column has the IDENTITY property it becomes more difficult.
The remedy is to remove the IDENTITY property.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Robert Ludig]

I am using C# and ADO.NET to acess the database. I have a Hashtable
that contains all the Steps wich should be insterted (100-10000). I
suppose it is a bad idea to create one big instertion command (as
string) and the do a OleDbCommand.ExecuteNonQuery() ? How can I insert
all at once in a clever way by using ADO.NET ?

[Erland Sommarskog]

Robert Ludig (sc******************@gmx.de) writes:

I am using C# and ADO.NET to acess the database. I have a Hashtable
that contains all the Steps wich should be insterted (100-10000). I
suppose it is a bad idea to create one big instertion command (as
string) and the do a OleDbCommand.ExecuteNonQuery() ? How can I insert
all at once in a clever way by using ADO.NET ?

That's not bad at all. Most of all it is effecient. If you insert rows
one by one, there will be many network roundtrips.

The one thing that is messy is to pass all the parameters. Of course,
you can build an SQL statement with the parameters expanded, but generally
you should stick to parameterised commands. It's not that difficult to
handle. You build the SQL statement in a loop, and add the SELECT for one
step, and then you add the parameters for that step as well.
The alternative is build an XML document and pass this to a stored
procedure, and then unpack the XML with OPENXML.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

Some DDL and sample would help. I sounds vaguely like a graph problem.