Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 43
  • Last Modified:

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)
0
vbnetcoder
Asked:
vbnetcoder
  • 6
  • 5
2 Solutions
 
É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
 
É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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Éric MoreauSenior .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
 
QlemoC++ DeveloperCommented:
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:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now