topic Re: How to BULK load into SQL Server using ODBC version 18? in Administration and Deployment

How to BULK load into SQL Server using ODBC version 18?

shparber_m — Sun, 01 Sep 2024 09:05:44 GMT

I've worked on dozens SAS projects that write into SQL Server using native SQL connector.

I've always specified bulkload=yes in the libname and the performance was great!

Now I have a customer that only has ODBC connector to SQL Server (latest version 18)

I cannot find the equivalent - I've tried many options, but no luck, it works very slow....

Is there a way to make the ODBC write into SQL Server fast in bulk?

Thank you

Re: How to BULK load into SQL Server using ODBC version 18?

SASKiwi — Sun, 01 Sep 2024 20:19:36 GMT

In my experience using SAS/ACCESS Interface to ODBC with SQL Server, the default settings affecting loading are not optimal. The two LIBNAME (or CONNECT) settings that work well for me are DBCOMMIT and INSERBUFF. Try DBCOMMIT = 0 and INSERTBUFF = 10000 and see how you get on.

I've never bothered with BULKLOAD as tuning DBCOMMIT and INSERTBUFF has always given good performance. But then again most of my loads are less than 1 million rows.

Re: How to BULK load into SQL Server using ODBC version 18?

shparber_m — Sun, 08 Sep 2024 11:39:02 GMT

Excellent!

INSERTBUFF works like a charm!

libname sqllbr odbc
NOPROMPT="DSN=ODBC_Name;UID=username;PWD=password;DATABASE=DB_Name;SCHEMA=dbo;" INSERTBUFF=32767
;

Thanks a lot!