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

[shparber_m]

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

[SASKiwi · Accepted solution]

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.

[shparber_m]

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!