CODEWITHSUNDEEP
sqlsql-server-2005
Gonzalo
Gonzalo

Reputation: 55

How can I insert result from stored procedure to a new table?

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

Answers (1)

TT.
TT.

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

Related Questions