SQL Server uses one statement block to insert multiple records in batches.

SQL Server uses one statement block to insert multiple records in batches.

In our daily operations, we have to INSERT multiple records to a table in the database at a time, but the first thought is to copy and paste N multiple repeated insert into statements, in case an error occurs when a semicolon or multiple Commas are missing, it will be a waste of time and work.

In addition to the method mentioned above, there are actually two methods, which are simpler than the previous one.

The first is the previous method:

Copy codeThe Code is as follows: insert into MyTable (ID, NAME) VALUES (1, '20140901 ');
Insert into MyTable (ID, NAME) VALUES (2, '20140901 ');
Insert into MyTable (ID, NAME) VALUES (3, '20140901 ');

The second method is to use union all for insert operations:

Copy 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 one!

The third method is a bit special. It is exclusive to SQL Server. Therefore, if you are not SQL Server, you cannot use this method. So please upgrade it! Experience the benefits of SQL Server2008.

Copy codeThe Code is as follows: insert into MyTable (ID, NAME)
VALUES (7, '003 '), (8, '004'), (9, '005 ')

The above demonstrates three different methods to insert multiple data records into the database at a time. The third method looks simple and efficient. We recommend that you upgrade it to SQL Server.