Using SQLCMD with Variables

sqlcmdssms

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:

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.