asked on

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)

Éric Moreau

not when using the EXEC with dynamic queries

vbnetcoder

ASKER

so there is no way to read the results of a dynamic query into a table that has not already been created?

Éric Moreau

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.

SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vbnetcoder

ASKER

@openrowset was as stupidly named variable. It is a select statement

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Éric Moreau

Have you tried the real OpenRowset method from the links already provided?

vbnetcoder

ASKER

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)

Éric Moreau

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

vbnetcoder

ASKER

Yes. I will make it #

Éric Moreau

but then it won't work in EXEC because it won't be global!

vbnetcoder

ASKER

ok i will think of something :)