Using SQLCMD with Variables

Question

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

Answer 1

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:

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

Run the script then go and have a look in the temp.sql file. Mine looks like this:

temp.sql output

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.

• mortenstarck: Hi @wBob You example make totally sense. But then it also looks like an hack to get around things. I was hoping that i could use the way i was trying above to insert into master database and use the id from that to insert it as part of sequence.