sql server - Stored Procedure result into temp table in Azure Data Warehouse -


in azure data warehouse, have stored procedure return result of select command.

how push stored procedure result temp table?

i tried below query , returning error message.

create table #temp (name varchar(255), created_date datetime) go insert #temp exec sp_testproc 

output message:

msg 103010, level 16, state 1, line 3 parse error @ line: 2, column: 1: incorrect syntax near 'exec'. 

azure sql data warehouse not support insert ... exec per here. however, temp tables have different scope means can viewed outside stored procedures create them. create temp table inside stored proc, , can viewed once stored proc has executed, eg:

if object_id('dbo.usp_gettablenames') not null drop proc dbo.usp_gettablenames; go  create proc dbo.usp_gettablenames      -- drop table if exists     if object_id('tempdb..#tables') not null drop table #tables;      -- create temp table viewing outside stored procedure     create table #tables     (         [object_id]     int not null,         name            sysname not null     )         (         distribution = hash([object_id]),         heap     );       insert #tables     select object_id, name     sys.tables;  go  -- run proc exec dbo.usp_gettablenames; go   -- table table still available reading outside scope of stored procedure select * #tables;  drop table #tables; go 

a similar example available in 'modularizing code' section of this article. it's different order of doing things.


Comments

Popular posts from this blog

account - Script error login visual studio DefaultLogin_PCore.js -

xcode - CocoaPod Storyboard error: -