Posted by Erland Sommarskog on 05/19/06 10:31

Robert Ludig (schwertfischtrombose@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: SQL Script Debugging - Just being curious
• Prev in forum: Insert into a table recursively
• Thread view: Re: Insert into a table recursively

[Reply to this message]