How to implement a for each loop in SQL

Question

How to implement a for each loop in SQL?

I am looking for a way to automate the backing up of databases for SQL Server 2019 to a given folder, with the following name syntax:

<Date><DB Name>

I am hoping to be able to a list of all databases in a temporary table:

DECLARE @DBList TABLE (DBName VARCHAR(40))

INSERT INTO @DBList 
    SELECT name 
    FROM master.dbo.sysdatabases

and then run each record agents function.

At the moment this is how I do it. It works is a vile violation of the write-once principle

-- Initialize variables
DECLARE @Date NVARCHAR(MAX) = CONVERT(VARCHAR, GETDATE(), 112);
DECLARE @DBName SYSNAME;
DECLARE @Path NVARCHAR(MAX);

USE [master];

-- BackUp Database
SET @DBName = 'DATABASE01';
SET @Path = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\' + @Date + @DBName +'.bak';
BACKUP DATABASE @DBName TO DISK = @Path;

SET @DBName = 'DATABASE02';
SET @Path = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\' + @Date + @DBName +'.bak';
BACKUP DATABASE @DBName TO DISK = @Path;

SET @DBName = 'DATABASE03';
SET @Path = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\' + @Date + @DBName +'.bak';
BACKUP DATABASE @DBName TO DISK = @Path;

SET @DBName = 'DATABASE04';
SET @Path = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\' + @Date + @DBName +'.bak';
BACKUP DATABASE @DBName TO DISK = @Path;

SET @DBName = 'DATABASE05';
SET @Path = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\' + @Date + @DBName +'.bak';
BACKUP DATABASE @DBName TO DISK = @Path;

score 2 · Accepted Answer · answered Jul 04 '19 at 20:28

You can make use of loop in this way. You need to create some sort of ranking to go through each row, and then get the value based on current rank.

DECLARE @DBList TABLE (DBName varchar(40),rankvalue int)
insert into @DBList SELECT name, DENSE_RANK() over (order by name) rankval FROM master.dbo.sysdatabases


 Declare @Date NVARCHAR(max) = convert(varchar, getdate(), 112);
DECLARE @DBName SYSNAME;
DECLARE @Path NVARCHAR(MAX);
 Declare @current int = 1   , @maxvalue int =(select max(rankvalue) from  @DBList) 


while @current <= @maxvalue 
begin 
Set @DBName =  (select DBName  from @DBList where rankvalue = @current)
Set @Path = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\' + @Date + @DBName +'.bak';
BACKUP DATABASE @DBName TO DISK = @Path   
set @current = @current + 1 
end

How to implement a for each loop in SQL

1 Answers1