topic Re: SAS Table upload into SQL Server through ODBC in SAS Programming

SAS Table upload into SQL Server through ODBC

BaileyY — Mon, 18 Aug 2025 21:02:41 GMT

Hello,

I am trying to upload around 7 million data record rows into a SQL Server database. It is taking over 3 hours and the job is still running. Any help would be greatly appreciated. I do not have any special code that I am writing into SQL. Its just basically a SAS temp table uploading into SQL Server database.

My code is simply this :

Data Server1.DiagnosisTable;

Set DiagnosisTable; Run;

Re: SAS Table upload into SQL Server through ODBC

SASKiwi — Mon, 18 Aug 2025 21:37:29 GMT

Setting the INSERTBUFF option to a value of at least 10000 should speed things up drastically. Usually you apply that on the LIBNAME statement connecting to SQL Server. Database inserts are best done with PROC DATASETS and the APPEND statement in my experience.

Re: SAS Table upload into SQL Server through ODBC

Ksharp — Tue, 19 Aug 2025 01:55:38 GMT

And PROC COPY could get you faster:

proc copy in=work out=Server1;
select DiagnosisTable;
run;

Re: SAS Table upload into SQL Server through ODBC

BaileyY — Tue, 19 Aug 2025 02:25:34 GMT

OMFG- using this and the proc copy as suggested by KSharp reduced my data upload time from 5 hours to just 10 minutes. thank you so very very much.

Re: SAS Table upload into SQL Server through ODBC

BaileyY — Tue, 19 Aug 2025 02:26:37 GMT

Thank you so very much. Using the InsertBuffer along with this proc copy dropped my upload time from hours to just 10 minutes. Thank you so so much!!

Re: SAS Table upload into SQL Server through ODBC

SASKiwi — Tue, 19 Aug 2025 02:36:55 GMT

That sounds good for 5 million rows.

Re: SAS Table upload into SQL Server through ODBC

jennifertaylor — Tue, 19 Aug 2025 04:47:37 GMT

Increase the INSERTBUFF option (≥10000) on your LIBNAME to speed up inserts. Using PROC DATASETS with APPEND is usually the fastest method.