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




No comments:

Post a Comment

PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. whe...