SAS Table upload into SQL Server through ODBC

[BaileyY]

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;

[SASKiwi · Accepted solution]

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.

[BaileyY]

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.

[jennifertaylor]

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

[Ksharp]

And PROC COPY could get you faster:

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

[BaileyY]

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!!

[SASKiwi]

That sounds good for 5 million rows.