In Azure Data Warehouse, i have a Stored Procedure which will return the result of SELECT command.
How to push the Stored Procedure result into temp table?
I tried the below query and it is returning an error message.
CREATE TABLE #temp
(name varchar(255), created_date datetime)
GO
INSERT INTO #temp
EXEC sp_testproc
Output message:
Msg 103010, Level 16, State 1, Line 3
Parse error at line: 2, column: 1: Incorrect syntax near 'EXEC'.
Azure SQL Data Warehouse does not support INSERT ... EXEC as per here. However, temp tables also have a different scope which means they can be viewed outside stored procedures that create them. Simply create your temp table inside the stored proc, and it can be viewed once the stored proc has executed, eg:
IF OBJECT_ID('dbo.usp_getTableNames') IS NOT NULL DROP PROC dbo.usp_getTableNames;
GO
CREATE PROC dbo.usp_getTableNames
AS
-- Drop table if already exists
IF OBJECT_ID('tempdb..#tables') IS NOT NULL DROP TABLE #tables;
-- Create temp table for viewing outside stored procedure
CREATE TABLE #tables
(
[object_id] INT NOT NULL,
name SYSNAME NOT NULL
)
WITH
(
DISTRIBUTION = HASH([object_id]),
HEAP
);
INSERT INTO #tables
SELECT object_id, name
FROM sys.tables;
GO
-- Run the proc
EXEC dbo.usp_getTableNames;
GO
-- The table table is still available for reading outside the scope of the stored procedure
SELECT *
FROM #tables;
DROP TABLE #tables;
GO
A similar example is available in the 'Modularizing code' section of this article. It's just a slightly different order of doing things.
