December 2, 2011

Insert result of stored procedure into temp table SQL Server

How can we insert stored procedure result in a temporary table.
CREATE PROC spGetDateList
AS
BEGIN
    SELECT GETDATE()   AS [DATE]  UNION ALL
    SELECT GETDATE()+1 AS [DATE]  UNION ALL
    SELECT GETDATE()+2 AS [DATE]  UNION ALL
    SELECT GETDATE()+3 AS [DATE]  UNION ALL
    SELECT GETDATE()+4 
    
END
GO
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
SELECT * INTO #TempTable FROM 
OPENROWSET('SQLNCLI', 'Server=ServerInstanceName;Trusted_Connection=yes;',
'EXEC Database.Schema.StoredProcedure')

That’s It
Enjoy Learning.
 

No comments:

Post a Comment