SQL Puzzle | Inserted Rows Count Puzzle

This question was asked to me in one of the technical interviews I had recently attended. In this case they are inserting data in a table, Now this table has only one row and they are inserting data with single insert statement with GO.

Please check out the sample input values and sample expected output below.

Sample Input

For example we are inserting 100 rows in a table we should get 100 as output.

Expected Output

100

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION – 1 | Using Output Clause & Temp Table

--

CREATE TABLE #TempNew 
( 
	Cnt INT NOT NULL
)
GO

CREATE TABLE testOutputClause
(
	Id INT IDENTITY PRIMARY KEY
)
GO

INSERT INTO testOutputClause 
OUTPUT Inserted.Id INTO #TempNew
DEFAULT VALUES
GO 100

SELECT COUNT(*) FROM #TempNew

--

—

[/code]

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com