proc sql connect to odbc - how to insert records in sql table

[bmillson1]

proc sql;
connect to ODBC as mycon (datasrc='DB');

EXECUTE (SET IDENTITY_INSERT tableX ON ) by mycon;
INSERT INTO tableX (id,name,city)
SELECT (id,name,city) FROM work.tableY;

EXECUTE (SET IDENTITY_INSERT tableX OFF ) by mycon;
disconnect from mycon;
quit;

- DB is a sql database reference via odbc

- tableX is an existing table in the sql database

- id is a primary key

- work.tableY is a temp dataset in SAS that I want to use to update tableX in sql

The code runs with no errors, but the problem is it creates a temp dataset in SAS as work.tableX.  I want it to insert the records in tableX in sql.  Clearly I'm missing a step that defines tableX as a table in sql.

Thanks.

[SASKiwi]

You can't use SQL passthru to load a SAS table into an external database as the SQL runs purely in that database and knows nothing about SAS tables.

A good way to solve this problem is to use PROC DATASETS with a LIBNAME assigned to the external database:

libname MyODBC ODBC noprompt = '<put your ODBC connection string here>';

proc datasets library = MyODBC;
  append base = tableX data = work.tableY;
run;
quit;

[Kurt_Bremser]

Since you do not use a reference to the ODBC connection in the important part of the SQL, it is equivalent to just this:

proc sql;
INSERT INTO tableX (id,name,city)
SELECT (id,name,city) FROM work.tableY;
quit;

You need to define a library connection to the SQL Server and use that in the INSERT INTO clause.

[bmillson1]

Thanks for the quick replies!

When I add the reference to the sql table using libname, I get an error. I've confirmed the libname process works as I can do other tasks successfully. What I ultimately need to achieve is adding the id,name,city to v.tableX (from work.tableY). Wondering if part of the issue is that id is a primary key? Either way, I need to insert the id from work.tableY as it's from a prod db and affects mappings to other tables. So I don't want to the use the libname option of ignore_read_only_columns=yes, as the id's aren't sequential. That's why I'm trying to use IDENTITY_INSERT, plus the insert code, in the single proc sql.  

libname v odbc dsn=DB user=sa pwd=admin schema=dbo;

proc sql;
connect to ODBC as mycon (datasrc='DB');

EXECUTE (SET IDENTITY_INSERT v.tableX ON ) by mycon;
INSERT INTO v.tableX (id,name,city)
SELECT (id,name,city) FROM work.tableY;

EXECUTE (SET IDENTITY_INSERT v.tableX OFF ) by mycon;
disconnect from mycon;
quit;

ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the object "v.tableX" because it does
not exist or you do not have permissions.

[LinusH]

Sorry, I don't follow you argument of why not to use the libname.

Perhaps you can try to use the libname, see what happens. Either it proves your point (which is?), or showed that it's actually working for your use case.

[ccaulkins9]

@bmillson1
you need to talk to your SQL Server DBA(s) and have them both update that password - admin(?!) - and grant you insert privileges either on that tableX or the whole "forking" schema. 😉 (: