Reputation: 55
I have a stored procedure that returns a SELECT
(can't convert it into a view because it does a little bit more than a SELECT
). I want to store the results into a new non temporary table.
So far this is what I've tried
select *
into newTable
from (StoredProcedure) t
But it throws an error:
Incorrect syntax near ')'.
EDIT: My SP is
CREATE PROCEDURE Soporte.ManejoSPs
@NombreSP nvarchar (200)
AS
declare @qry nvarchar (500)
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @NombreSP + 'Table')
BEGIN
set @qry = 'select top 0 into ' + @NombreSP + 'Table from (@NombreSP) t'
print @qry
exec(@qry)
END
declare @Ultima datetime
declare @Tabla nvarchar(200)
declare @Minutos int
Select @Minutos = Minutos, @Tabla = NombreTabla, @Ultima = UltimaVez from Soporte.ManejoSP where NombreSP = @NombreSP
If (datediff(mi, @Ultima, getdate()) > @Minutos)
BEGIN
set @qry = 'INSERT INTO ' + @Tabla + ' exec ' + @NombreSP
exec(@qry)
END
set @qry = 'Select * from ' + @Tabla
exec(@qry)
Upvotes: 3
Views: 7281
Reputation: 16143
Note: The following will only work up until SQL Server 2008 R2. For SQL Server 2012+ this will not work (requires WITH RESULT SETS
, see here for some details on its specification).
If your stored procedure is called t
(in database db_name
and schema schema_name
) and the table newTable
doesn't exist yet:
SELECT
*
INTO
newTable
FROM
OPENROWSET (
'SQLNCLI',
'Server=localhost;Trusted_Connection=yes;',
'SET FMTONLY OFF; EXEC [db_name].[schema_name].t;'
);
If the server is a named instance, you need to supply the proper Server parameter (ServerName\Instance
).
For this to work, you need the execute following first to allow Ad Hoc Distributed Queries
. You only need to execute this once.
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
If the newTable
table already exists:
INSERT INTO newTable
EXEC t
Upvotes: 3