SAS - Insert statement into existing SQL table

Question

Here is my code:

PROC SQL;
connect to odbc (dsn=ODC uid=sa pwd=XXXXX);
EXECUTE ( INSERT INTO dbo.tblDLA_Backup SELECT * FROM &dlafile.) BY ODBC;
disconnect from odbc;
quit;

Im getting this error

ERROR: CLI execute error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'work.dlabackup'.

If i do this:

proc sql;
connect to odbc (dsn=ODC uid=sa pwd=XXXXX);


insert into tblDLA_Backup SELECT * FROM WORK.DLABACKUP;

disconnect from odbc;

quit;

I get this error:

ERROR: File WORK.TBLDLA_BACKUP.DATA does not exist.

Why is it that I can't reference my SAS dataset and just insert? it should be simple as that..

Answer 1

You need to use libref it you want to write into the foreign database.

libname sqldb odbc dsn=ODC uid=sa pwd=XXXXX ;
PROC SQL;
  INSERT INTO SQLDB.tblDLA_Backup SELECT * FROM &dlafile.;
quit;

Note that you also need fix it so that your macro variable contains the name of a table that exists.

Answer 2

The first error occurs because you are executing an SQL instruction on SQL Server, and not locally. And since this instruction contains a reference to your local SAS dataset, an error occurs because SQL server thinks it is a table in its own database.

You take a wrong approach on that one.

Your second approach is correct because you are executing the SQL in SAS, which both knows the local dataset and the SQL server tables. And it is syntactically valid at first glance.

The error is clear: SAS doesn't found the local dataset WORK.TBLDLA_BACKUP

Thus, verify if this dataset exists and is not corrupted:

in your explorer window, click on Libraries, then Work, and verify if TBLDLA_BACKUP is there, and if yes open it and check if you see your data.

I can't say more at this point, but you should hopefully discover something.