Execute SQL commands stored in database column

Question

I have a small bit of SQL that creates a temporary table, #List, with a column "Command".
The contents of the Command column are SQL commands ready to execute.

I'm beating my head against a wall, trying to: run through the table, one row at a time, executing the contents of the column. Anyone have any advice?

Thanks,

Answer 1

You can do this:

DROP TABLE IF EXISTS #List
CREATE TABLE #List (Command varchar(max), OrderBy INT IDENTITY(1,1))

INSERT INTO #List VALUES
('SELECT * FROM Table1'),
('SELECT * FROM Table2'),
('DELETE FROM Table1'),
('SELECT * FROM Table1')


DECLARE @sqlcmd VARCHAR(MAX);

SET @sqlcmd = (
SELECT STRING_AGG(Command,';' + CHAR(10)) WITHIN GROUP (ORDER BY [OrderBy]) as cmd
FROM #List
)

EXEC(@sqlcmd);

Answer 2

Hi @David Crowder ,

Welcome to Microsoft Q&A!

Reusing Tom's DDL and sample data.

If your version of SQL Server is SQL 2016 and earlier, please refer below method.

DECLARE @sqlcmd VARCHAR(MAX);  
  
SELECT @sqlcmd = STUFF(( SELECT ';'+char(10)+ Command +char(10) FROM #List order by [OrderBy]  FOR XML PATH('') ), 1, 1, '')       
  
exec( @sqlcmd)  

In addition, you could also refer below loop.

DECLARE @sqlcmd VARCHAR(MAX)='';  
  
DECLARE @I INT;  
DECLARE @MAX INT;  
  
SET @I=1;  
SELECT @MAX = MAX(OrderBy) FROM #LIST;  
  
WHILE @I<=@MAX  
BEGIN  
	SELECT  @sqlcmd= Command FROM #LIST WHERE OrderBy=@I  
	EXEC (@sqlcmd)  
	--WAITFOR DELAY '00:00:02'  
    SET @I=@I+1  
	SET @sqlcmd=''  
END  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.