Data transfer from SAS EG to SQL Server

[RENATA1]

Greeting! Could you please assist me with improving my code in order to transfer data from SAS EG (version 7.15) to Microsoft SQL Server in most efficient and fastest way? What can I add to the libname statement to be able to transfer data faster – large datasets with over 200 million rows?

LIBNAME out_srv SQLSVR DATAsrc=&datasrc. USER="&userid." PASSWORD="&spwd." SCHEMA="&schema." DBCOMMIT=10000 INSERTBUFF=10000;

PROC SQL NOPRINT;

CONNECT TO SQLSVR(DATAsrc="&datasrc." USER="&userid." PASSWORD="&spwd.");

CREATE TABLE out_srv.table1 AS

SELECT * FROM WORK.TEMPFILE;

QUIT;

RUN;

[SASKiwi]

Would it be possible to change your process to do inserts into an existing table rather than create a new table every time?

How long does it take to load 200 million rows? Have you played with different DBCOMMIT and INSERTBUFF values and does it improve performance?

BTW the CONNECT statement in your code is redundant.

[RENATA1]

Thank you all very much for your input and suggestions! This process is new to me, I was hoping there is a suggestive number when it comes to insertbuff and/or dbcommit; but it sounds like getting that right number is only by ‘experimenting’ 😊

Today is day 3 and my job is still running. Is the process of creating an empty table first and then inserting records faster than what I am doing in my code?

[SASKiwi]

@RENATA1  - Wow, running for three days is definitely slow! You should experiment on a much smaller load table to optimise performance. 100,000 rows would be good to start with. 

I can recommend using PROC DATASETS with the APPEND statement for inserting rows into an existing table. Emptying rows from an existing SQL Server table is really fast using SQL passthru - a few seconds at most. I haven't compared the performance of this versus a CREATE TABLE but it is definitely worth a try.