Im trying to do some general script that i can use to run the same INSERT/UPDATE or DELETE statements on different site databases.
This was my first attempt which works just fine, but i have to copy and paste everything.
: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
But i like to have something where it's more general, were i don't have to copy and paste. I have tried something like this:
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;
I have also tried where ServerName and
Best Answer
You could try a "script the script" approach by using the SQLCMD
:out
command to redirect output, with:connect
and optionally:r
to read the output, something like this:Run the script then go and have a look in the
temp.sql
file. Mine looks like this:If you are happy with the output, uncomment the
:r
to run it, or just run/edit the script you have created.Hope that makes sense.