SQL Puzzle | INSERT multiple rows with single Insert Keyword | SQL Interview Question

OR

Different Methods to Insert multiple rows using single Insert Keyword

In this post we will understand what are the different methods for inserting records from a table, each of them has its own pros and cons.

The condition here it that you can only use the INSERT keyword once.

Notes –
1. Most interviewers asks this question in SQL interview.
2. These will help in your SQL development and unit testing tasks as you will get how quickly you can insert the data based on your requirement.

The Methods are-

1. Using UNION ALL
2. Using Comma Separated
3. Using SELECT and Comma
4. Using GO
5. Using Loop

Method 1. Insert multiple ROWS using single INSERT Keyword – Using UNION ALL

In this method you have to write many statements including UNION ALL & SELECT.

--USING UNION ALL

CREATE TABLE testMultipleInsertUNIONALL
(
 ID INT
,NAME VARCHAR(1)
)
GO

INSERT INTO testMultipleInsertUNIONALL
SELECT 1,'A'
UNION ALL
SELECT 2,'B'
UNION ALL
SELECT 3,'C'
UNION ALL
SELECT 4,'D'
UNION ALL
SELECT 5,'E'
UNION ALL
SELECT 6,'F'

--

Method 2. Insert multiple ROWS using single INSERT Keyword – Using Comma Separated

In this method you have to pass different rows using comma separated values. This is one of the best and the mostly used method across.

Note – This is the most widely used method to insert multiple rows.

--

--USING COMMA SEPERATED

CREATE TABLE testMultipleInsertCommaSeperated
(
	 ID INT
	,NAME VARCHAR(1)
)
GO

INSERT INTO testMultipleInsertCommaSeperated(ID,NAME)
VALUES
(1,'A'),
(2,'B'),
(3,'C'),
(4,'D'),
(5,'E'),
(6,'F')

--

3. Using SELECT and Comma

We can use below method also to insert multiple rows using single insert keyword. Although this method is not widely used.

--

--USING SELECT AND COMMA

CREATE TABLE testMultipleInsertSelectComma
(
	 ID INT
	,NAME VARCHAR(1)
)
GO

INSERT INTO testMultipleInsertSelectComma VALUES
(
	(SELECT 1), (SELECT 'Z')
)
,
(
	(SELECT 2), (SELECT 'K')
)
,
(
	(SELECT 3), (SELECT 'Q')
)
,
(
	(SELECT 4), (SELECT 'I')
)
,
(
	(SELECT 5), (SELECT 'M')
)
GO

--

4. Using GO

Using this method we can only insert multiple rows with same values. This will be of great help is you just have to insert default values. Please check out the sample example below.

--

--USING GO Statement

CREATE TABLE testMultipleInsertGO
(
	 ID INT IDENTITY(1,1)
	,DATES DATETIME DEFAULT GETDATE()
)
GO

INSERT INTO testMultipleInsertGO DEFAULT VALUES
GO 5

Once you run the above example below output will come.

5. Using Loop

Using While loop also we can insert multiple rows in a table. Please find the sample SQL statements below.

--

CREATE TABLE testMultipleInsertLoop
(
	 ID INT IDENTITY(1,1)
	,DATES DATETIME DEFAULT GETDATE()
)
GO

DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 10)
BEGIN
   INSERT INTO testMultipleInsertLoop DEFAULT VALUES;
   SET @a = @a + 1;
END;
GO

--

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

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