Create table from Exec | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Create table from Exec

It is possible to move the result set of Exec to the table if the table is already created
Insert into mytable(col1,…coln) Exec(query). This works well But is it possible to have
Select * into mytable from Exec(query)
Any ideas?
Madhivanan Failing to plan is Planning to fail
The SELECT INTO statement creates a new table and populates it with the result set of the SELECT. The structure of the new table is defined by the attributes of the expressions in the select list, for example:
SELECT Shippers.*, Link.Address, Link.City,
Link.Region, Link.PostalCode
INTO NewShippers
FROM Shippers
JOIN LinkServer.DB.dbo.Shippers AS Link
ON (Shippers.ShipperID = Link.ShipperID) SELECT INTO can be used to combine data from several tables or views into one table. It can also be used to create a new table containing data selected from a linked server. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
But it is not possible when I try to create new table from Exec(query)
Does this mean that it does not recognize the structure of what Exec() return?
If it works in the first case, why is it not possible for the second case?
Madhivanan Failing to plan is Planning to fail
No, SELECT ..EXEC is simply no supported syntax. —
Frank Kalis
SQL Server MVP

The reason why I want to do is I have stored procedure in which I pass query as parameter. I want to compare the result set with other table. But it will be possible only if I move the resultset to temp table. I got other solution. I used Select * into temptable inside Exec and it works well Madhivanan Failing to plan is Planning to fail