SAS Proc SQL Database Table Insert

Question

Using SAS's Proc SQL, is there a way to insert records from a SAS Dataset into a table in the open SQL Server connection? Something like this (which doesn't work):

proc sql exec;
    connect to sqlservr as DataSrc (server=my-db-srvr database=SasProcSqlTest);

 create table Items as select * from connection to DataSrc (
  SELECT * FROM tblItem
 );

    update Items
    set Name = Name + Name,
        Value * 2;

    insert into tblItem (Name, Value)
    select Name, Value
    from Items;

    disconnect from DataSrc;quit;run;
quit;
run;

Answer 1

To my knowledge, using pass through SQL constrains you to the database server. The SAS documentantion says that you should preferrably create a library reference to the database and then treat the database tables just like SAS tables. In your case this means just normal proc sql. This should work at least in the latest SAS versions, but for large tables is not optimal.

What we've done to circumvent this is

1. Create a table in a temporary database - the table should not be session-specific
2. Bulk load data from SAS to the created table, using proc append
3. Do the pass through update
4. Drop the table in temp db.

Answer 2

You can do what you want within an open connection..

create an associated libname..

libname datasrc_lib sqlservr server=my-db-srvr database=SasProcSqlTest;

proc sql exec;
    connect to sqlservr as DataSrc (server=my-db-srvr database=SasProcSqlTest);

        create table Items as select * from connection to DataSrc (
                SELECT * FROM tblItem
        );

    update Items
    set Name = Name + Name,
        Value * 2;

    insert into datasrc_lib.some_temp_table select * from items;


    execute( insert into tblItem where select * from some_temp_table ) by DataSrc ;

    execute( drop table some_temp_table ) by DataSrc ;


    disconnect from DataSrc;quit;run; quit; run;

The above pseudo code should give you an idea of how it should work. You may need to create the "some_temp_table" in the proc sql as well or have a permanent staging table available.