Bulk insetion in sql (Using Output Inserted Clause)

Hi All,

This is very good sql query for understanding bulk insertion with “OUTPUT INSERTED CLAUSE” and “WITH CLAUSE”. You can do it using only “OUTPUT CLUASE” but here I have used “WITH CLAUSE” so you can also understand the “WITH CLAUSE” functionality.

See below query.

DECLARE @tbl TABLE(ID INT IDENTITY, firstCol INT, secondCol INT)
DECLARE @tempTbl TABLE(tempID INT, firstCol INT)

;WITH tempWith AS
(SELECT 100 AS tempID
UNION ALL
SELECT tempID + 1 FROM tempWith WHERE tempID + 1 < 110
)

INSERT INTO @tbl(firstCol, secondCol)
OUTPUT INSERTED.ID, INSERTED.secondCol INTO @tempTbl
SELECT tempID, tempID+10 FROM tempWith

SELECT * FROM @tbl
SELECT * FROM @tempTbl