Solved

select into table

Posted on 2016-07-15
12
38 Views
Last Modified: 2016-07-15
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
Comment
Question by:vbnetcoder
  • 6
  • 5
12 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41712826
not when using the EXEC with dynamic queries
0
 

Author Comment

by:vbnetcoder
ID: 41712840
so there is no way to read the results of a dynamic query into a table that has not already been created?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41712845
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 250 total points
ID: 41712850
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
 

Author Comment

by:vbnetcoder
ID: 41712852
@openrowset was as stupidly named variable. It is a select statement
0
 
LVL 69

Accepted Solution

by:
Qlemo earned 250 total points
ID: 41712855
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41712864
Have you tried the real OpenRowset method from the links already provided?
0
 

Author Comment

by:vbnetcoder
ID: 41712865
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41712869
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
 

Author Comment

by:vbnetcoder
ID: 41712872
Yes. I will make it #
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41712874
but then it won't work in EXEC because it won't be global!
0
 

Author Comment

by:vbnetcoder
ID: 41712878
ok i will  think of something :)
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Job Hung 17 35
SQL server client app 3 25
Need help with convert Informix SQL SELECT statement to Microsoft SQL 1 25
Access #Deleted data 20 34
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question