select into table

In this code i am creating the table with code.  Is there any way i could alter the code to SELECT INTO so i don't have to explicitly create the table
?

CREATE TABLE  ##TESTTABLE ([RoomID] varchar(50),  [BuildingID] varchar(100), [Number] varchar(100), [Name] varchar(100))
EXEC (@openrowset)


INSERT INTO ##TESTTABLE([RoomID] ,  [BuildingID] , [Number], [Name] )
EXEC (@openrowset)
vbnetcoderAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
As the referenced article says: it works with a global temporary table (but not with a local temporay table) to use EXEC. So your dynamic SQL could look like
select ... INTO ##TESTTABLE from ...

Open in new window

But of course you'll need to drop the global temp table yourself, the same as when creating it above.
0
 
Éric MoreauSenior .Net ConsultantCommented:
not when using the EXEC with dynamic queries
0
 
vbnetcoderAuthor Commented:
so there is no way to read the results of a dynamic query into a table that has not already been created?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Éric MoreauSenior .Net ConsultantCommented:
what is your @OpenRowSet?

Instead of EXEC, would you be able to do a "select * from OpenRowSet" as shown in https://msdn.microsoft.com/en-us/library/ms190312.aspx?

If you can do this, you will be able to use the INSERT INTO statement and not create a table first.
0
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
and BTW, do you know the meaning of ## ? A global temp table is not widely used. Have a look at http://sqlmag.com/t-sql/temporary-tables-local-vs-global
0
 
vbnetcoderAuthor Commented:
@openrowset was as stupidly named variable. It is a select statement
0
 
Éric MoreauSenior .Net ConsultantCommented:
Have you tried the real OpenRowset method from the links already provided?
0
 
vbnetcoderAuthor Commented:
I got it working with your help. All i needed to do was to update the dymanic sql with the select into and then run EXEC (@openrowset)
0
 
Éric MoreauSenior .Net ConsultantCommented:
but if you are still using ##, you might have troubles if 2 users are running the same code at the same time because the temp table is global to all users
0
 
vbnetcoderAuthor Commented:
Yes. I will make it #
0
 
Éric MoreauSenior .Net ConsultantCommented:
but then it won't work in EXEC because it won't be global!
0
 
vbnetcoderAuthor Commented:
ok i will  think of something :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.