Creating temp table in sql server pass through?

[Anotherdream]

Hello all. Does anyone happen to know if it's possible to create a temporary table within a sql server pass through. Aka, is there a way to make the following code work?

proc sql;

connect to odbc as myodbc (dsn=odbc_MYODBC_yay);

create table finalresults as

select * from connection to myodbc

(select *

into #temporaryTable

from mastertable

where mycolumn='0001'

select * from #temporarytable);

disconnect from myodbc;

quit;

Obviously this is a simplified version of the code I would like to write, however if possible it would results in much easier code to maintain, transfer, and a general efficency gain.

I should pre-face this with the note that I know a workaround for this. It's simple to create a stored procedure to do everything you need, and then pull from the table created within the stored procedure, and then drop the table (or a few other work arounds I've used before).

SO I don't need a solution to the problem per-say, I just need to know if this exact solution is someway possible!

Thanks!

Thanks so much!

Brandon

[SASKiwi · Accepted solution]

Yes, no problem. Check out the EXECUTE statement:

proc sql;

connect to odbc as myodbc (dsn=odbc_MYODBC_yay);

execute(create table #temporaryTable as

             select from mastertable

            where mycolumn='0001') by myodbc;

quit;

You can run any SQL Server-specific statements this way, even stored procedures.

[Anotherdream]

So I have a few issues with doing things this way. The first is, if I define a temporary table with two ## (a global temporary table) I cannot then go into sql server and open this temporary table (or create a second sql server pass through functionality to pull data from the intermediate temporary table.

I actually need to create 3-4 temporary tables, and then pull the final results into a SAS dataset, where that result is simply the select from the last temporary table created.

Brandon

[alandool]

Hi Brandon,

Did you ever receive a reply or figure out how to do this?  I have a need to do something similar.

"I actually need to create 3-4 temporary tables, and then pull the final results into a SAS dataset, where that result is simply the select from the last temporary table created."

Alan

[Tom]

I am not sure about using ODBC, but with other SAS/Access products you can keep the temporary tables by keeping your connection to the server open using a LIBNAME.

LIBNAME xxx odbc ..... ;

proc sql ;

connect to odbc .... ;

execute ( ... ) by odbc ;

quit;

[Anotherdream]

I did alandool. It appears that if you make independent execute queries as SASKiwi does above this can be done. Just make x different queries, 1 for each temp table plus 1 additional to pull it into the sas system!

[jakarman]

Why would you try to outsmart a SQL optimizer either at the SAS site or at the DBMS site?

SQL is using temporary tables when it decides they are needed.

[alandool]

Thanks, Anotherdream and Tom!   I was able to do what was needed by enclosing sql statements as mentioned:

exec(  ......   ) by sqlsvr;