如何在SQL中为每个循环实现一个

Question

如何在SQL中为每个循环实现A？

我正在寻找一种自动化SQL Server 2019数据库备份到给定文件夹的方法，并具有以下名称语法：

<Date><DB Name>

我希望能够在临时表中列出所有数据库的列表：

DECLARE @DBList TABLE (DBName VARCHAR(40))
INSERT INTO @DBList 
    SELECT name 
    FROM master.dbo.sysdatabases

然后运行每个记录代理功能。

目前这就是我的做法。它起作用是对写入原理的一种恶毒侵犯

-- 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 FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLBackup' + @Date + @DBName +'.bak';
BACKUP DATABASE @DBName TO DISK = @Path;
SET @DBName = 'DATABASE02';
SET @Path = 'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLBackup' + @Date + @DBName +'.bak';
BACKUP DATABASE @DBName TO DISK = @Path;
SET @DBName = 'DATABASE03';
SET @Path = 'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLBackup' + @Date + @DBName +'.bak';
BACKUP DATABASE @DBName TO DISK = @Path;
SET @DBName = 'DATABASE04';
SET @Path = 'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLBackup' + @Date + @DBName +'.bak';
BACKUP DATABASE @DBName TO DISK = @Path;
SET @DBName = 'DATABASE05';
SET @Path = 'C:Program FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLBackup' + @Date + @DBName +'.bak';
BACKUP DATABASE @DBName TO DISK = @Path;

Answer 1

您可以以这种方式使用循环。您需要创建某种排名来浏览每一行，然后根据当前等级获取值。

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 FilesMicrosoft SQL ServerMSSQL13.MSSQLSERVERMSSQLBackup' + @Date + @DBName +'.bak';
BACKUP DATABASE @DBName TO DISK = @Path   
set @current = @current + 1 
end