CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005
Control Freak
Control Freak

Reputation: 13233

Inserting the Identity of another Insert from Select?

Is This Possible?

Here is something I'm looking for, executed together:

First, it would execute the INSERT based on how many rows in the SELECT

  INSERT INTO TABLE2 (xID, NAME)
  SELECT xID, NAME FROM TABLE

Then getting the @@IDENTITY of each INSERTED ROW, it would create a new Insert including the same data of the first SELECT statement:

  INSERT INTO TABLE3 (xID, NAME, ID)
  SELECT xID, NAME, ID as Scope_IdentitY()

If not, what the best way without using cursor or while?

Upvotes: 0

Views: 166

Answers (5)

user756519
user756519

Reputation:

You can declare a table variable and store the output of the rows inserted into dbo.Table2 in this variable and use the table variable as the input for table dbo.Table3.

CREATE TABLE dbo.Table1
(
        xid     int NOT NULL
    ,   name    varchar(30) NOT NULL
);

CREATE TABLE dbo.Table2
(
        id      int NOT NULL IDENTITY
    ,   xid     int NOT NULL
    ,   name    varchar(30) NOT NULL
);

CREATE TABLE dbo.Table3
(
        id      int NOT NULL
    ,   xid     int NOT NULL
    ,   name    varchar(30) NOT NULL
);

INSERT INTO dbo.Table1 (xid, name) VALUES
    (195, 'abc'),
    (242, 'def'),
    (332, 'ghi');
GO

DECLARE @tempTable table
    (       id      int
        ,   xid     int
        ,   name    varchar(30)
    );

INSERT dbo.Table2
    OUTPUT INSERTED.id, INSERTED.xid, INSERTED.name
        INTO @tempTable
        SELECT xid, name FROM dbo.Table1;

INSERT dbo.Table3 (id, xid, name) 
    SELECT id, xid, name FROM @tempTable;

SELECT id, xid, name FROM dbo.Table2;
SELECT id, xid, name FROM dbo.Table3;

GO

Upvotes: 1

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

You have, at least, two options:

1) The OUTPUT...INTO target_table clause (SQL2005+)

2) Or you could write composable DML(SQL2008+).

Example:

DECLARE @Table2 TABLE(
    ID  INT IDENTITY PRIMARY KEY, --IDENTITY 
    xID INT NOT NULL,
    NAME VARCHAR(25) NOT NULL
);

DECLARE @Table3 TABLE(
    ID  INT PRIMARY KEY, --No IDENTITY 
    xID INT NOT NULL,
    NAME VARCHAR(25) NOT NULL
);

--First solution: OUTPUT ... INTO
INSERT  INTO @Table2 (xID, NAME)
OUTPUT  inserted.xID, inserted.NAME, inserted.ID INTO @Table3(xID, NAME, ID)
SELECT  t.Col1, t.Col2
FROM    (SELECT 11,'A' UNION ALL SELECT 22,'B' UNION ALL SELECT 33,'C') AS t(Col1,Col2);

--Second solution: composable DML
INSERT  INTO @Table3(xID, NAME, ID)
SELECT  src.xID, src.NAME, src.ID
FROM
(
        INSERT  INTO @Table2 (xID, NAME)
        OUTPUT  inserted.xID, inserted.NAME, inserted.ID 
        SELECT  t.Col1, t.Col2
        FROM    (VALUES(44,'D'),(55,'E'),(66,'F')) AS t(Col1,Col2)
) src

SELECT * FROM @Table2 
SELECT * FROM @Table3

Upvotes: 3

Adrian J. Moreno
Adrian J. Moreno

Reputation: 14859

Assuming these table structures:

TABLE_A
-----------
X_ID
NAME

TABLE_B
----------------
TABLE_B_ID [PK]
X_ID
NAME

TABLE_C
----------------
TABLE_C_ID [PK]
X_ID
NAME
TABLE_B_ID [FK]

Then wouldn't this work (best in a transaction)?:

-- Grab data from TABLE_A and INSERT INTO TABLE_B
INSERT INTO TABLE_B (
    X_ID,
    NAME
)
SELECT
    X_ID,
    NAME
FROM
    TABLE_A

-- Grab data from TABLE_B that matches the data imported from TABLE_A
-- and INSERT that data into TABLE_C (incl. the PK from TABLE_B)

INSERT INTO TABLE_C (
    X_ID,
    NAME,
    TABLE_B_ID
)
SELECT 
    b.X_ID,
    b.NAME,
    b.TABLE_B_ID
FROM
    TABLE_B b
INNER JOIN 
    TABLE_A a ON a.X_ID = b.X_ID

Upvotes: 0

John Dewey
John Dewey

Reputation: 7093

INSERT INTO TABLE2 (xID, NAME)
OUTPUT
INSERTED.xID, INSERTED.NAME, INSERTED.ID
INTO TABLE3 (xID, NAME, ID)
SELECT xID, NAME FROM [TABLE]

Upvotes: 2

therealmitchconnors
therealmitchconnors

Reputation: 2758

OK, based on your comments below, try this:

  INSERT INTO TABLE2 (xID, NAME)
  SELECT xID, NAME FROM TABLE;

  INSERT INTO TABLE3 (xID, NAME, ID)
  SELECT xID, NAME, @@identity
  FROM TABLE2;

Upvotes: 0

Related Questions