Creating temp table in sql server pass through?

Accepted Solution Solved
Reply
Super Contributor
Posts: 418
Accepted Solution

Creating temp table in sql server pass through?

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


Accepted Solutions
Solution
‎05-29-2013 09:11 PM
Super User
Posts: 3,250

Re: Creating temp table in sql server pass through?

Posted in reply to Anotherdream

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.


View solution in original post


All Replies
Solution
‎05-29-2013 09:11 PM
Super User
Posts: 3,250

Re: Creating temp table in sql server pass through?

Posted in reply to Anotherdream

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.


Super Contributor
Posts: 418

Re: Creating temp table in sql server pass through?


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

Contributor
Posts: 25

Re: Creating temp table in sql server pass through?

Posted in reply to Anotherdream

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

Super User
Super User
Posts: 7,039

Re: Creating temp table in sql server pass through?

Posted in reply to Anotherdream

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;

Super Contributor
Posts: 418

Re: Creating temp table in sql server pass through?

Posted in reply to 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!

Trusted Advisor
Posts: 3,212

Re: Creating temp table in sql server pass through?

Posted in reply to Anotherdream

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.

---->-- ja karman --<-----
Contributor
Posts: 25

Re: Creating temp table in sql server pass through?

Posted in reply to Anotherdream

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

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 5587 views
  • 0 likes
  • 5 in conversation