Three ways SQL server uses one statement block to BULK insert multiple records _mssql2008

In our daily operations, we inevitably have a table on the database, to insert more than one record, but the first thought is to copy, paste n more than repeated insert into the statement, in case one less a semicolon, or more than a comma, and so on, to create errors, to find can be exhausted, not only waste time, and delayed work.

In addition to the method mentioned above, there are actually two methods, compared to the previous one should be concise.

The first is the previous method:

Copy Code code as follows:

INSERT into MyTable (id,name) VALUES (1, ' 123 ');
INSERT into MyTable (id,name) VALUES (2, ' 456 ');
INSERT into MyTable (id,name) VALUES (3, ' 789 ');

The second method, using union All, inserts the operation:

Copy Code code as follows:

INSERT into MyTable (id,name)
SELECT 4, ' 000 '
UNION All
SELECT 5, ' 001 '
UNION All
SELECT 6, ' 002 '

Is it easier than the first method, which is said to be faster than the first one!

The third method is a bit special, is the SQL Server2008 unique, so, if you are not SQL Server2008, you can not use this method, so quickly upgrade it! Experience the benefits that SQL Server2008 has brought to us.

Copy Code code as follows:

INSERT into MyTable (id,name)
VALUES (7, ' 003 '), (8, ' 004 '), (9, ' 005 ')

There are 3 different ways to insert multiple data to a database at once, and the third method looks more concise and efficient, and recommends that you quickly upgrade to SQL Server2008.