Insert multiple rows using single Insert statement in SQL Server

This is a very basic problem sometimes newbies struggle with. I have seen developers writing 10 different insert statements to insert 10 rows in a table.  This can be simplified in multiple ways as shown below

CREATE TABLE tblinsert (Sno INT IDENTITY,col1 CHAR(1))
GO
-- using union all
-- applies to all version
INSERT INTO tblinsert 
SELECT 'A'
UNION ALL
SELECT 'B'
UNION ALL
SELECT 'C'
GO
-- Method 2: Applies to SQL Server 2008 and above 
INSERT INTO tblinsert VALUES ('D'), ('E'),('F')

 

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Leave a Reply

Your email address will not be published. Required fields are marked *