SQL PUZZLE | Multiple ways to Insert multiple rows to a single table having only 1 identity column | SQL Interview Question

In this post we shall understand what are the multiple ways to Insert multiple rows to a single table having only 1 identity column. Please check out sample input and expected output.

Sample Input

Expected output

Script – DDL and INSERT sample data

--

CREATE TABLE IdentityTable
(
	Id INT IDENTITY(1,1)
)
GO

--

SOLUTION 1 | USING Merge Statement

--

MERGE INTO IdentityTable a
USING ( SELECT top 10 * FROM Master..spt_values ) T
ON 1 = 0
WHEN NOT MATCHED THEN INSERT DEFAULT VALUES;

--

OUTPUT

--

Id
-----------
1
2
3
4
5
6
7
8
9
10

(10 rows affected)

--

SOLUTION 2 | USING GO Statement

--

INSERT INTO IdentityTable DEFAULT VALUES
GO 10

--

OUTPUT

--

Id
-----------
1
2
3
4
5
6
7
8
9
10

(10 rows affected)

--

SOLUTION 3 | USING While LOOP and IDENTITY_INSERT OFF

--

DECLARE @Start INT = 1, @TotalRows INT
SET @Start = 1
SET @TotalRows = 10

SET IDENTITY_INSERT IdentityTable ON;

WHILE @Start <= @TotalRows
BEGIN
    INSERT INTO IdentityTable(Id) 
	SELECT @Start    
	SET @Start = @Start + 1
END
SET IDENTITY_INSERT IdentityTable OFF;

--

OUTPUT

--

Id
-----------
1
2
3
4
5
6
7
8
9
10

(10 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com