[SOLVED] Azure Synapse: Output param execution into a temporary table

Azure Synapse: Output param execution into a temporary table

I have the following script which works perfectly fine in SQL Server, but fails in Azure.

declare @comm nvarchar(max)
declare @table table(comm nvarchar(max))

    set @comm=' select '' ALTER TABLE '' + s.name + ''.'' + t.name + '' ALTER COLUMN      '' + c.name + '' '' + y.name + '' NOT NULL;  '' as Cmd  
                from        sys.tables   t   
                inner join  sys.schemas  s on t.schema_id = s.schema_id
                inner join  sys.columns  c on c.object_id = t.object_id
                inner join  sys.systypes y on c.system_type_id=y.xtype ' 
                 

    insert into @table(comm)
        execute (@comm);

In Synapse there is no table type, so I would like to output into a temp table, but the following does not work:

    declare @comm nvarchar(4000)
    
    set @comm=' select '' ALTER TABLE '' + s.name + ''.'' + t.name + '' ALTER COLUMN      '' + c.name + '' '' + y.name + '' NOT NULL;  '' as Cmd  
                from        sys.tables   t   
                inner join  sys.schemas  s on t.schema_id = s.schema_id
                inner join  sys.columns  c on c.object_id = t.object_id
                inner join  sys.systypes y on c.system_type_id=y.xtype '
                

    Create table #comm (cmd varchar(8000))
    insert into #comm  
    execute(@comm);

my problem is specifically with the syntax for the last 2 lines in Synapse. Any thoughts?

I tried executing the second statement with Incorrect syntax near 'into' error, I'm trying to get a table with alter commands as rows.

Solution

In Synapse dedicated SQL pool, you can use the SELECT INTO statement to create a temporary table and insert the results of a SELECT statement into it. You can change your script in the following way to output the results into a temporary table.

declare  @comm nvarchar(4000)
set  @comm=' select '' ALTER TABLE '' + s.name + ''.'' + t.name + '' 
ALTER COLUMN '' + c.name + '' '' + y.name + '' NOT NULL; '' 
as Cmd from sys.tables t inner join sys.schemas s on 
t.schema_id = s.schema_id inner join sys.columns c on 
c.object_id = t.object_id inner join sys.systypes y on 
c.system_type_id=y.xtype ' 
DECLARE  @sql nvarchar(max) = N' SELECT Cmd INTO #comm FROM ('  +  @comm  +  ') AS t;'; 
EXECUTE sp_executesql @sql; 
SELECT  *  FROM #comm;

This should produce the ALTER TABLE commands as rows in a temporary table #comm with a single field named Cmd.