Three ways to insert multiple records in batches using one statement block

In our daily operations, we must insert multiple records at once for a certain table in the database, but the first thing we think of is to copy and paste N multiple repeated INSERT INTO statements. If one of them has a semicolon or multiple commas and errors, it will take a lot of effort to find them, which is a waste of time and work.

In addition to the methods mentioned above, there are actually methods two of them, which are more concise than the previous one.

First of all, the previous method:

Copy the codeThe code is 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 is to use UNION ALL to perform the insertion operation:

Copy the codeThe code is 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? It is said to be faster than the first method!

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

Copy the codeThe code is as follows:

INSERT INTO MyTable(ID,NAME)

VALUES(7,'003'),(8,'004'),(9,'005')

The above demonstrates 3 different methods to insert multiple pieces of data into the database at a time. The third method looks simple and efficient. It is recommended that everyone upgrade to SQL Server 2008 as soon as possible.