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? How can I improve my code or 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;

[Shmuel]

Usually the communication line transfer is fast but the bottle neck is the I/O - read from hard disk and write to hard disk. You may look for options how to assign more buffers at sas side and if possible on sql server side too. 

[Sajid01]

Agreeing completely with what Shmuel has said, I would suggest take computing to where the data resides(In database processing) 
Either use SQL pass through or perform the processing to the extent possible in SQL server..
Here are some papers on the subject
https://www.mwsug.org/proceedings/2014/SA/MWSUG-2014-SA03.pdf

https://www.mssqltips.com/sqlservertip/2703/using-sas-access-and-proc-sql-to-retrieve-sql-server-dat... 

http://support.sas.com/resources/papers/proceedings10/300-2010.pdf 

[RENATA1]

Thank you both 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’ 😊

Is the process of creating an empty table first and then inserting records faster than what I am doing in my code?

[Shmuel]

I have no experience with SQL server, anyhow creating an empty table is usually very fast. 

Most of the time is the I/O time of copying data from one HD to another.

Is it possible to replace the HD to a faster model? or use some other protocols for connection, enabling parallel transfer ? I'm not expert of those methods.