Smarter way to use SQLCMD with dynamic calling

Question

I have this MSSQL SQLCMD code, which can logon the database and in thisjust do an SELECT statement:

   :CONNECT czasql-001
   SELECT * FROM [Lps_Hepper_Cz].[config].[LpsPlant]
  GO

    :CONNECT LS_Hepper_DK

    SELECT * FROM [LPS_Hepper_NY].[config].[LpsPlant]

    :CONNECT LS_Hepper_DK
    SELECT * FROM [LPS_Hepper_DK].[config].[LpsPlant]

    :CONNECT LS_Hepper_DK
    SELECT * FROM [LPS_Hepper_SUPPLIER].[config].[LpsPlant]
    GO

           :CONNECT LS_Hepper_372
    SELECT * FROM [LPS_Hepper_MO].[config].[LpsPlant]
    GO

           :CONNECT LS_Hepper_678
    SELECT * FROM [LPS_Hepper_678].[config].[LpsPlant]
    GO

This solution works okay, but requires me to copy paste the copy multiple times to change the database name. But can some one help me improve this by using an loop instead. I have tried by using a temporary table. Like so:

 declare @tbl table (ServerName nvarchar(50), DbName nvarchar(50), IsDone bit default(0))
 insert into @tbl (ServerName,DbName) VALUES ('CZASQL-001', '[Lps_Hepper_CZ]')
 insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_DK',  '[Lps_Hepper_DK]')
 insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_DK',  '[Lps_Hepper_NY]')
 insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_DK',  '[Lps_Hepper_Supplier]')
 insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_372', '[Lps_Hepper_MO]')
 insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_678', '[Lps_Hepper_678]')

But i cant figure out to to do the logic for iterate through the database an connect to the server and set the database name, when the data i have is as an nvarchar? Do anybody have an suggestion?

UPDATE: I'm only going to use this for INSERT/UPDATE or DELETE. So im not using a stored procedure. I like to use this for updating data on all our databases. That's why I'll only use the temp table which should be part of the script.

Servernames and database names will come from the temp table above. Table name are the same on all database's, due to we have copies of the database worldwide to limit data download i need to make sure that when i update one, that i also updates the rest.

Update 2: I have tried to play around with it, but i keep stalling, with not being able to use the values in the temp table. Meaning that i can't use the :SETVAR to set the servername:

  declare @tbl table (ServerName nvarchar(50), DbName nvarchar(50), IsDone bit default(0))
     insert into @tbl (ServerName,DbName) VALUES ('CZASQL-001', '[Lps_Hepper_CZ]')
     insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_DK',  '[Lps_Hepper_DK]')
     insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_DK',  '[Lps_Hepper_NY]')
     insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_DK',  '[Lps_Hepper_Supplier]')
     insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_372', '[Lps_Hepper_MO]')
     insert into @tbl (ServerName,DbName) VALUES ('LS_Hepper_678', '[Lps_Hepper_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
        SELECT ServerName --This looks correctly
        print CONVERT(NVARCHAR(100),@selectedRow)
        :CONNECT ServerName

           USE @DatabaseName
           GO
          SELECT * FROM [config].[LpsPlant]
         GO

         UPDATE @tbl SET IsDone = 1 WHERE Id = @selectedRow
    END;

Answer 1

I'm only going to use this for INSERT/UPDATE or DELETE. So im not using a stored procedure. I like to use this for updating data on all our databases

If it is the case you could consider using Registered Servers:

Benefits of Registered Servers

With Registered Servers you can:

• Register servers to preserve the connection information.

• Determine if a registered server is running.

• Easily connect Object Explorer and Query Editor to a registered server.

• Edit or delete the registration information for a registered server.

• Create groups of servers.

• Provide user-friendly names for registered servers by providing a value in the Registered server name box that is different from the Server name list.

• Provide detailed descriptions for registered servers.

• Provide detailed descriptions of registered server groups.

• Export registered server groups.

• Import registered server groups.

• View the SQL Server log files for online or offline instances of SQL Server

• Execute Statements Against Multiple Servers Simultaneously

You could create multiple groups based on: server/database/environment(DEV/PROD/QA).

Another possibility is to export registered servers to file, put in source control system (SVN/Git) and share with other developers.

Using SSMS you click new query based on server group and execute the same query on multiple databases at once.

^{Image from: http://sqlmag.com/site-files/sqlmag.com/files/archive/sqlmag.com/content/content/142469/Greenwood-SQL2331-Fig5-sm.jpg}