基于SQL结果的批处理文件循环 [英] Batch file looping based on SQL results

问题描述

所有，我正在寻找一些建议。



我需要在团队城市多次使用不同的数据库执行构建步骤（或Liquibase命令）。



困难在于需要根据以前的Sql语句确定数据库的名称。



原因是我们的项目在其master数据库的表中列出了许多当前的归档数据库。每个都需要在我们的开发环境中每天更新。



有人可以建议我调查的方法或方向吗？



非常感谢



我的尝试：



我考虑过批处理文件和powershell，但我对这些方法没有信心。



我不是在寻找有人为我做这项工作，只是建议调查的方向。

解决方案

一个选项是从sql查询中触发一个简单的批处理文件或脚本，而不是将查询结果传递给脚本。



例如

  SELECT 名称，ROW_NUMBER（） OVER （ ORDER   BY  name） as  rn 
  INTO  #TEMP 
  FROM  master.dbo.sysdatabases 
  WHERE  name  NOT   IN （'  master'，'  tempdb'，'  model'，' < span class =code-string> msdb'）; 
 
  DECLARE   @ i   int  =  1    -   循环计数器 
  DECLARE   @ m   int  =（ SELECT  MAX（rn） FROM  #TEMP）  -   循环结束 
  DECLARE   @ d   nvarchar （ 125 ）  -   数据库名称 
  DECLARE   @cmd   nvarchar （ 512 ）  -   执行命令 
  WHILE   @ i < =  @ m  
  BEGIN  
  SELECT   @ d  =（ SELECT 名称来自 #TEMP  WHERE  rn =  @ i ）
  SET   @ cmd  = '   c：\ temp \DOIT.bat' +  @ d  
  EXEC  master..xp_CMDShell  @ cmd  
  SET   @ i  =  @ i  +  1  
  END  

您将替换c：\ temp \DOIT。用你想用来执行命令的任何东西（例如批处理文件中的liquibase命令行）。



如果你在尝试使用xp_CMDShell时遇到错误那么你可以按如下方式启用它

  EXEC  sp_configure ' 显示高级选项'， 1 ;   -   启用要更改的选项 
  GO  
  RECONFIGURE ;   -   使用此更改进行更新 
  GO  
  EXEC  sp_configure '  xp_cmdshell' ， 1 ;   -   启用xp_CMDShell  
  GO  
  RECONFIGURE ;   -   使用此更改进行更新 
  GO < /跨度> 

All, I am looking for some advice.

I need to execute build steps (or Liquibase command) on team city a number of times with different databases.

The difficulty is that the names of the databases need to be determined based on a prior Sql statement.

The reason for this is that our project lists a number of current archive databases in a table in its master database. Each of which needs to be updated daily within our development environment.

Can anybody suggest possible methods or direction for me to investigate?

Most appreciated

What I have tried:

I have considered batch files and powershell but I am not confident about these methods.

I am not looking for somebody to do the work for me, simply suggestions of directions to investigate.

解决方案

One option would be to fire a simple batch file or script from a sql query rather than passing the results of the query to the script.

E.g.

SELECT name, ROW_NUMBER() OVER(ORDER BY name) as rn
INTO #TEMP
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');

DECLARE @i int = 1 -- loop counter
DECLARE @m int = (SELECT MAX(rn) FROM #TEMP) -- end of loop 
DECLARE @d nvarchar(125) -- database name
DECLARE @cmd nvarchar(512) -- command to execute
WHILE @i <= @m
BEGIN
	SELECT @d = (SELECT name from #TEMP WHERE rn = @i)
	SET @cmd = 'c:\temp\DOIT.bat ' + @d
	EXEC master..xp_CMDShell @cmd
	SET @i = @i + 1
END

Where you would replace c:\temp\DOIT.bat with whatever you want to use to execute the commands (liquibase command line in a batch file for example).

If you get an error attempting to use xp_CMDShell then you can enable it as follows

EXEC sp_configure 'show advanced options', 1;	-- enable options to be changed
GO
RECONFIGURE; -- update with this change
GO
EXEC sp_configure 'xp_cmdshell', 1; -- enable xp_CMDShell
GO
RECONFIGURE; -- update with this change
GO

这篇关于基于SQL结果的批处理文件循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！