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.