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?
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 
INSERT INTO TABLE2 (xID, NAME) OUTPUT INSERTED.xID, INSERTED.NAME, INSERTED.ID INTO TABLE3 (xID, NAME, ID) SELECT xID, NAME FROM [TABLE] 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 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; 
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