使用带有变量的SQLCMD
使用带有变量的SQLCMD
提问于 2016-05-02 06:41:35
我试图做一些通用脚本，我可以使用它在不同的站点数据库上运行相同的INSERT/UPDATE或DELETE语句。这是我第一次尝试，但我必须复制和粘贴一切。
:CONNECT czasql-001
USE [Lps_FinishPack_Cz]
GO
SELECT * FROM [config].[LpsPlant]
GO
:CONNECT LS_LPS_DK
use [LPS_FINISHPACK_NY]
GO
SELECT * FROM [config].[LpsPlant]
use [LPS_FINISHPACK_DK]
GO
SELECT * FROM [config].[LpsPlant]
use [LPS_FINISHPACK_SUPPLIER]
GO
SELECT * FROM [config].[LpsPlant]
GO
:CONNECT LS_LPS_372
use [LPS_FINISHPACK_MO]
GO
SELECT * FROM [config].[LpsPlant]
GO
:CONNECT LS_LPS_678
use [LPS_FINISHPACK_678]
GO
SELECT * FROM [config].[LpsPlant]
GO但我想要的东西更一般，如果我不需要复制和粘贴。我试过这样的方法：
DECLARE @tbl TABLE (Id int IDENTITY (1, 1),ServerName sysname, DbName sysname, IsDone BIT DEFAULT(0))
INSERT INTO @tbl (ServerName,DbName) VALUES (N'CZASQL-001', N'[Lps_FinishPack_CZ]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_DK', N'[Lps_FinishPack_DK]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_DK', N'[Lps_FinishPack_NY]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_DK', N'[Lps_FinishPack_Supplier]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_372', N'[Lps_FinishPack_MO]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_678', N'[Lps_FinishPack_678]')
WHILE (SELECT COUNT(*) FROM @tbl WHERE IsDone = 0) > 0
BEGIN
DECLARE @selectedRow INT = (SELECT TOP 1 Id FROM @tbl WHERE IsDone = 0)
--DECLARE @ServerName NVARCHAR(50)= (SELECT ServerName FROM @tbl WHERE Id = @selectedRow)
--DECLARE @DatabaseName NVARCHAR(50) = (SELECT DbName FROM @tbl WHERE Id = @selectedRow)
DECLARE @ServerName sysname= (SELECT ServerName FROM @tbl WHERE Id = @selectedRow)
DECLARE @DatabaseName sysname = (SELECT DbName FROM @tbl WHERE Id = @selectedRow)
--:SETVAR DatabaseName @DatabaseName
--:SETVAR ServerName @ServerName
print CONVERT(NVARCHAR(100),@selectedRow)
:CONNECT @ServerName
USE @DatabaseName
GO
SELECT * FROM [config].[LpsPlant]
GO
UPDATE @tbl SET IsDone = 1 WHERE Id = @selectedRow
END;我也尝试过ServerName和
回答 1
Database Administration用户
发布于 2016-05-02 11:21:35
您可以通过使用SQLCMD :out命令重定向输出来尝试一种“脚本脚本”方法，使用:connect和可选的:r来读取输出，如下所示：
SET NOCOUNT ON
DECLARE @tbl TABLE (Id int IDENTITY (1, 1),ServerName sysname, DbName sysname, IsDone BIT DEFAULT(0))
INSERT INTO @tbl (ServerName,DbName) VALUES (N'CZASQL-001', N'[Lps_FinishPack_CZ]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_DK', N'[Lps_FinishPack_DK]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_DK', N'[Lps_FinishPack_NY]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_DK', N'[Lps_FinishPack_Supplier]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_372', N'[Lps_FinishPack_MO]')
INSERT INTO @tbl (ServerName,DbName) VALUES (N'LS_LPS_678', N'[Lps_FinishPack_678]')
:out d:\temp\temp.sql
SELECT
':connect ' + serverName + '
USE ' + dbName + '
SELECT * FROM [config].[LpsPlant]
GO
'
FROM @tbl
GO
:out STDOUT
GO
-- Optionally read/run the output
--:r d:\temp\temp.sql运行脚本，然后开始查看temp.sql文件。我的看起来是这样的：
如果您对输出感到满意，请取消对:r的注释以运行它，或者只运行/编辑您创建的脚本。
希望这是合理的。
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/137169
复制相关文章
相似问题
腾讯云开发者
