How to Insert Multiple Rows at Once in T-SQL

Sometimes you want a statement that will insert multiple rows in a table all at once. It’s easy to do with MySQL, but here comes T-SQL – how do I do this? There are a number of ways to do that, most rather unattractive unless your database is Microsoft SQL Server 2008.

Let’s examine them, using this table structure:

CREATE TABLE Person
(
	Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
	FirstName NVARCHAR(50),
	LastName NVARCHAR(50)
)

The most obvious thing you can do is to have multiple INSERT statements that will populate this table:

INSERT INTO Person (FirstName, LastName) VALUES ('Johnny', 'Beck')
INSERT INTO Person (FirstName, LastName) VALUES ('Jenny', 'Cook')
INSERT INTO Person (FirstName, LastName) VALUES ('Tod', 'Lance')
INSERT INTO Person (FirstName, LastName) VALUES ('Rob', 'Wright')
INSERT INTO Person (FirstName, LastName) VALUES ('Dianna', 'Berg')

However, what if you wanted to populate all of this 5 rows in a single statement? – you can do so by performing a UNION operation on a set of data returned from SELECT statements:

INSERT INTO Person (FirstName, LastName)
	(SELECT 'Johnny', 'Beck')
		UNION ALL
	(SELECT 'Jenny', 'Cook')
		UNION ALL
	(SELECT 'Tod', 'Lance')
		UNION ALL
	(SELECT 'Rob', 'Wright')
		UNION ALL
	(SELECT 'Dianna', 'Berg')

Looks like more typing but it’s a single statement now so you don’t have to deal with transactions.

Now, if you are working with SQL Server 2008, there is a way which is much like doing this in MySQL – finally:

INSERT INTO Person (FirstName, LastName)
	VALUES
		('Johnny', 'Beck'),
		('Jenny', 'Cook'),
		('Tod', 'Lance'),
		('Rob', 'Wright'),
		('Dianna', 'Berg')

Now, that’s what it’s supposed to look like, finally implemented in SQL Server 2008.

And, of course keep in mind that if you are inserting rows that you selecting from another table or tables, you can always use INSERT/SELECT combination:

INSERT INTO Person (FirstName, LastName)
	SELECT FirstName, LastName
		FROM Friends

Try it out and have fun SQL’ing!

This entry was posted in MSSQL and tagged , , , , , . Bookmark the permalink.

Leave a Reply

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