CODEWITHSUNDEEP
sqlsql-serversql-server-2008insertidentity
JohnDoDo
JohnDoDo

Reputation: 4900

SQL Server insert with row N referencing the identity of the N - 1 row. Possible?

I have a SQL Server 2008 DB with a table like this (Table1):

ID    ParentID    Name
--    --------    ---
11    NULL        Foo
12    11          Bar
13    12          etc

ID is declared with IDENTITY.

I have the values Foo, Bar, etc as rows in another table (Table2) and I must insert them in Table1.

The inserted values must be in a parent child relation in Table1, with ParentID column from row N pointing to ID of row N-1.

Is it possible with one statement to insert the values with the relations between them?

Upvotes: 1

Views: 451

Answers (2)

Jeremy Pridemore
Jeremy Pridemore

Reputation: 1995

Since you asked if you could do this in one statement, here is an answer for that. I can't help but feel that if you had given more information I would be telling that whatever you're doing this for should be solved another way. I'm having a hard time coming up with a good reason to do this. Here is a way to do it regardless though:

I am assuming Table1 has Id, ParentId, and Name, and that Table2 has Id and Name (you said you got the names Foo, Bar, whatever from Table2). I'm also assuming there is some order you can impose.

CREATE TABLE #T
(
    Id INT IDENTITY(1, 1)
    , ParentId INT
    , Name VARCHAR(100)
)

CREATE TABLE #T2
(
    Id INT IDENTITY(1, 1)
    , Name VARCHAR(100)
)

INSERT #T2
(
    Name
)
VALUES ('Foo'), ('Bar')

INSERT #T
(
    ParentId
    , Name
)
SELECT
    NULLIF(IDENT_CURRENT('#T')
    + ROW_NUMBER() OVER(ORDER BY T2.Name)
    - 2, (SELECT ISNULL(MIN(Id), 1) - 1 FROM #T))
    , T2.Name
FROM #T2 T2

SELECT * FROM #T

DROP TABLE #T
DROP TABLE #T2

Upvotes: 0

Andomar
Andomar

Reputation: 238048

-- Insert all names in first table
insert  Table1
        (Name)
select  Name
from    Table2

-- For each row in Table1,
-- Search for the matching row in Table2,
-- Then look up the "parent" row in Table2,
-- And back to Table1 for the "parent" id
update  t1
set     ParentID = t1_parent.ID
from    Table1 t1
join    Table2 t2
on      t1.Name = t2.name
cross apply
        (
        select  top 1 *
        from    Table2 t2_parent
        where   t2_parent.ID < t2.ID
        order by
                t2_parent.ID desc
        ) t2_parent
join    Table1 t1_parent
on      t1_parent.Name = t2_parent.Name

Upvotes: 3

Related Questions