CODEWITHSUNDEEP
sql-servert-sqlazure-synapse
Julaayi
Julaayi

Reputation: 509

Executing scripts as XML in Azure Synapse Analytics

I am not able to execute a script by parsing it as XML as mentioned here. I see that it is not supported in Synapse based on MSDN. Could someone please help me how could I achieve this in SQL DW? I have a table that have all the SQL Statements and I want to execute them by looping through each record in that column.

DDL:

CREATE TABLE [#Temp]
(
JOB_ID INT NOT NULL,
SQL_STMNT NVARCHAR(2000)
);

DML:

INSERT INTO #Temp
SELECT 100, 'SELECT * FROM Table_A WHERE MyDate = @ProcessDate'
UNION 
SELECT 103, 'SELECT * FROM Table_B WHERE MyDate = @ProcessDate AND MyCode = ''ABC'''
UNION 
SELECT 110, 'SELECT * FROM Table_C WHERE MyDate = @ProcessDate AND MyID = 345'
UNION 
SELECT 200, 'SELECT * FROM Table_D WHERE MyDate = @ProcessDate'
UNION 
SELECT 230, 'SELECT * FROM Table_E WHERE MyDate = @ProcessDate'

Upvotes: 1

Views: 1143

Answers (1)

GregGalloway
GregGalloway

Reputation: 11625

You can use the STRING_AGG function:

DECLARE @sql nvarchar(max) = (
 SELECT STRING_AGG(CAST(SQL_STMNT as nvarchar(max)), '; ') WITHIN GROUP (ORDER BY JOB_ID ASC)
 FROM #Temp
);
exec(@sql);

Upvotes: 1

Related Questions