Y
Y
Yu Yu2014-08-28 18:56:13
SQL
Yu Yu, 2014-08-28 18:56:13

What does batch data insertion mean?

Hello!
I need to insert a lot of data into the database (SQL server) at a time. I use:

INSERT INTO [dbo].[Main] ([ID_CHANNEL] ,[ID_RECDATE] ,[MESVALUE])
     VALUES (1 ,@d ,Item(ELIMS.ELIMSQUE.tVAL1))
INSERT INTO [dbo].[Main] ([ID_CHANNEL] ,[ID_RECDATE] ,[MESVALUE])
     VALUES (2 ,@d ,Item(ELIMS.ELIMSQUE.tVAL2))	 
INSERT INTO [dbo].[Main] ([ID_CHANNEL] ,[ID_RECDATE] ,[MESVALUE])
     VALUES (3 ,@d ,Item(ELIMS.ELIMSQUE.tVAL3))
INSERT INTO [dbo].[Main] ([ID_CHANNEL] ,[ID_RECDATE] ,[MESVALUE])
     VALUES (4 ,@d ,Item(ELIMS.ELIMSQUE.tVAL4))
INSERT INTO [dbo].[Main] ([ID_CHANNEL] ,[ID_RECDATE] ,[MESVALUE])
     VALUES (5 ,@d ,Item(ELIMS.ELIMSQUE.tVAL5))
INSERT INTO [dbo].[Main] ([ID_CHANNEL] ,[ID_RECDATE] ,[MESVALUE])
     VALUES (6 ,@d ,Item(ELIMS.ELIMSQUE.tVAL6))	 
INSERT INTO [dbo].[Main] ([ID_CHANNEL] ,[ID_RECDATE] ,[MESVALUE])
     VALUES (7 ,@d ,Item(ELIMS.ELIMSQUE.tVAL7))	 
INSERT INTO [dbo].[Main] ([ID_CHANNEL] ,[ID_RECDATE] ,[MESVALUE])
     VALUES (8 ,@d ,Item(ELIMS.ELIMSQUE.tVAL8))	 
INSERT INTO [dbo].[Main] ([ID_CHANNEL] ,[ID_RECDATE] ,[MESVALUE])
     VALUES (9 ,@d ,Item(ELIMS.ELIMSQUE.tVAL9))

I read here
habrahabr.ru/post/208264
that this is a problem. Am I doing the right thing? How can you organize the insertion of a large dataset?

Answer the question

In order to leave comments, you need to log in

2 answer(s)
A
Andrey Burov, 2014-08-28
@BuriK666

something like this

INSERT INTO [dbo].[Main] ([ID_CHANNEL] ,[ID_RECDATE] ,[MESVALUE])
 VALUES (1 ,@d ,Item(ELIMS.ELIMSQUE.tVAL1)), (2 ,@d ,Item(ELIMS.ELIMSQUE.tVAL2))

S
svd71, 2014-08-28
@svd71

The main DML processing time is working with a transaction: start, creating a version of records, commit / rollback.
Batch processing means processing multiple commands in a batch.
Most often it is based on the fact that for such a group of commands (let's say for 500 inserts) one transaction is used.
In some DBMS (usually a blocker), this method can also be achieved through the use of execute statement - when a group of commands is executed as one operation.
In others, this is a possible disabling of logging operations (bulk loading), which may make it impossible to roll back changes.
PS: SQL Server is a general concept. It is better to specify which one: MySQL SQL Server, MS SQL, Firebird, Sybase ASA/ASE, ....etc. They are all SQL Servera.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question