topic Re: Bulk loading in Administration and Deployment

Bulk loading

Wiki-Ben — Thu, 16 Apr 2026 13:07:38 GMT

Hi,

We are looking to improve data inserts in a SQL Server database (ODBC). In the SAS server connection, we have the advanced option to set 'Bulk Load' to Yes:

but we can also set this advanced option in the ODBC setup.

Do we have to set it in both?

Thx.

Re: Bulk loading

SASKiwi — Thu, 16 Apr 2026 20:09:22 GMT

You should also try setting the INSERTBUFF option as the default value is not optimal. Try INSERTBUFF = 10000 as a starting point but feel free to experiment with higher or lower values.

Re: Bulk loading

LinusH — Fri, 17 Apr 2026 06:24:56 GMT

I don't have the "Bulk Load" tab in my advanced settings, probably because we are using SAS/ACCESS to SQL Server, not ODBC. I'm on 9.4M8 Linux.
Not sure what you refer to when you say "advanced option in ODBC set up". Do you mean ODBC Manager (Ican't fins an advanced option here)?
I think it should be enough to set it on the libname, the important thing is to generate the BULKLOAD=YES when your LIBNAME statement is being generated.

Re: Bulk loading

Patrick — Sat, 18 Apr 2026 09:16:27 GMT

@Wiki-Ben The screen shot looks like DI Studio. Correct?

I've asked Copilot 365 about performance when using bulkload=yes with SAS Access to ODBC and the answer was interesting (though don't know if correct):

So just based on the Copilot answers, it's likely better to go with what @SASKiwi proposed (increase the insertbuff value, set dbcommit=0).
If you are using SAS Access to SQL Server then of course bulkload=yes could might make a difference, especially when loading into an empty table.

Bulkload=yes can be a libname or even a dataset option and though I would assume setting it in DIS will be sufficient on any level.

Re: Bulk loading

Wiki-Ben — Mon, 20 Apr 2026 11:32:41 GMT

Thank you all for your input,

@Patrick Used with SAS code (Libname/proc sql/data xxx) SAS Help Center: INSERTBUFF= Data Set Option

I guess test and trial will be the key here.

Thank you again,

Yvan