Oct 3, 2016

Insert multiple rows in single SQL Query

There are many different way you can insert multiple rows in single SQL Query.
When I was fresher, many times I face this question in interview, can you please write a script which can insert multiple rows. See following scripts which may help you.

Suppose we have a table Customer which contains FirstName, LastName.


CREATE TABLE #CUSTOMER(
FirstName VARCHAR(50),
LastName VARCHAR(50)
)


Script 1:


INSERT INTO #CUSTOMER(FirstName,LastName)
VALUES ('Dilip','Singh'),('Ashish','Singh'),('Vipul','Bhatt')

SELECT * FROM #CUSTOMER


Script 2:


INSERT INTO #CUSTOMER(FirstName,LastName)
SELECT 'Dilip','Singh'
UNION ALL
SELECT 'Ashish','Singh'
UNION ALL
SELECT 'Vipul','Bhatt'

SELECT * FROM #CUSTOMER


Script 3:


INSERT INTO #CUSTOMER(FirstName,LastName)
EXEC(
                        'SELECT ''Dilip'',''Singh''
                        SELECT ''Ashish'',''Singh''
                        SELECT ''Vipul'',''Bhatt'''
            )

SELECT * FROM #CUSTOMER