how to increase batch size to 100000

Question

how to increase batch size to 100000

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
December 27, 2019 at 3:36 pm

#3710775

i have a table where it has 1 million rows. i want to generate deployment script like insert script where it should add GO after each 10,000 records.

is there an option in sql server 2014 while generating scripts?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 1

If you're generating individual INSERT/VALUES statements, I wouldn't do that because it will take a month of Sundays to complete 1 million rows. In fact, most methods will not to mention the fact that such a thing will negate all possibility of minimal logging.

We need more information...

Are you, indeed, generating individual INSERT/VALUES statements using the GUI method of scripting data?
What does the CREATE TABLE statement look like for the target table?
Does the target table already contain any rows or is it empty at the start of the deployment?
Where will you be running this deployment script from? The command line or within SSMS?
I'm assuming that the data for the deployment script will live in a file. Is that correct? If not, can it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 2

What did you end up doing for this? Inquiring minds want to know. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63512 More actions · Answer 3

Couldn't you use TOP with OFFSET to get a fixed size chunk of records and insert that way? (So modify to do the insert in chunks?

use AdventureWorks2017;
GO
DECLARE @NumRows INT = 10;
WHILE @NumRows <= 40
BEGIN
 SELECT
  [name],
  listprice
 FROM
  production.product
 ORDER BY
  listprice,
  [name] 
 OFFSET @NumRows ROWS 
 FETCH NEXT 10 ROWS ONLY;
 
 SET @NumRows = @NumRows + 10;
END

Jonathan AC Roberts SSCoach Points: 18377 More actions · Answer 4

pietlinden wrote:

Couldn't you use TOP with OFFSET to get a fixed size chunk of records and insert that way? (So modify to do the insert in chunks?

I think the OP wanted the inserts in a million line script.

pietlinden SSC Guru Points: 63512 More actions · Answer 5

How about doing something like BCP OUT to write the records to some manageable number of text files, and then use BULK INSERT to insert each file of, say, 100K records?

https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver15

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 6

That was going to be my suggestion if the OP ever came back on this thread except I wouldn't limit the batches. First, BCP automatically limits itself to 1,000 rows per batch on the output (and the output should be sorted by an ORDER BY). Then, use BULK INSERT (which is a little bit faster than BCP) to do the import. It's nasty fast just about any way but if you can get minimal logging to happen with TABLOCK and an import order, you can get twice as fast as nasty fast across 2 million rows and not have to chunk up anything.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)