question

DavidCrowder-7659 avatar image
0 Votes"
DavidCrowder-7659 asked DavidCrowder-7659 commented

Execute SQL commands stored in database column

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,

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @DavidCrowder-7659,

Could you please validate the answers and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered DavidCrowder-7659 commented

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);
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Exactly what I was looking for!

Thank you!

0 Votes 0 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered DavidCrowder-7659 commented

Hi @DavidCrowder-7659,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

We're already using Tom's method. However, I like this a lot, too. This is very similar to my original code... but much nicer, and actually works (as opposed to my attempts).

Thank you!

0 Votes 0 ·