2

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

2

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.

| improve this answer | |
  • 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. – mortenstarck May 2 '16 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.