Select * into exec command doesn't work?

Question

I am using the OPENROWSET command to execute the query.

I want the result value must be inserted into the temp table like

SELECT * INTO #TempTable EXEC @query

but it shows an error of

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

Can anyone tell me how can I execute the query and insert into temp table?

The sample code is

declare @Query nvarchar(max)
SET @Query ='SELECT 
                *
            FROM
                OPENROWSET
                (
                        ''SQLNCLI'',
                        ''servername'';
                        ''db'';
                        ''pwd'',
                        ''set nocount on;
                        set fmtonly off;
                        exec databaseName.dbo.spname ''''param1 '''',''''param2'''',''''param3'''',''''param3''''  ''
                )
                '

select * into #temp exec sp_executesql @Query

To be clear you are using MSSQL and not MySQL? Creating alias like #TempTable is not possible in MySQL — Mad Dog Tannen, Jul 21 '14 at 09:46
Post the code of `@query`. Appears you are doing some aggregation in the query, but haven't provided any alias for the column — Raj, Jul 21 '14 at 09:46
can't you put your "into #temp" clause into the dynamic sql statement that you execute via sp_executesql? — PrfctByDsgn, Jul 21 '14 at 09:57
That I understand. If you want an answer, you need to ask correct questions. Post the code of that SP. — Raj, Jul 21 '14 at 10:01
that sp just return the slect statement. its executing correctly while executing like ' exec sp_executesql @Query '.I want to insert into the temp table in that scenario only i got this error. — jai, Jul 21 '14 at 10:17
possible duplicate of [How to SELECT \* INTO \[temp table\] FROM \[stored procedure\]](http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure) — Daniel E., Jul 21 '14 at 11:29

score 1 · Accepted Answer · answered Jul 21 '14 at 09:53

1

You must use following format:

DECLARE @T TABLE(Col1 int, col2 ...)

INSERT INTO @T
EXEC query

SELECT * FROM @T

For Example:

DECLARE @T TABLE(name NVARCHAR(100),minimum INT, maximum INT, configvalue INT, runvalue INT)

INSERT INTO @T
EXEC sys.sp_configure

SELECT * FROM @T
WHERE runvalue=1

answered Jul 21 '14 at 09:53

mehdi lotfi

11,194
18
82
128

That good but i cant determine which fields are coming from that query because its running dynamically – jai Jul 21 '14 at 09:55
tats why i tried to insert into the temp table using select * into exec @query – jai Jul 21 '14 at 09:57

Select * into exec command doesn't work?

1 Answers1