USE [distribution]

DECLARE @hyperscale NVARCHAR(500)='sql-triapharmamaprod-sqlserver01.database.windows.net';
DECLARE @tpl_archive NVARCHAR(MAX)='bcp.exe @tbl@ out d:\tsc\@tbl_file@_@host@.bcp -S @fqdn@ -T -d arizona -n';
DECLARE @tpl_rest NVARCHAR(MAX)='bcp.exe @dst@.@tbl@ in d:\tsc\@tbl_file@_@host@.bcp -S @hyperscale@ -T -d pharmacy -n';
DECLARE @tbl_full NVARCHAR(500);
DECLARE @tbl_only NVARCHAR(500);
DECLARE @tbl_file NVARCHAR(500);
DECLARE @dst_schema NVARCHAR(100);
DECLARE @fqdn NVARCHAR(500);
DECLARE @host NVARCHAR(255);
DECLARE @cmd TABLE(
    id INT IDENTITY
    , fqdn NVARCHAR(500) NOT NULL
    , tbl_name NVARCHAR(500) NOT NULL
    , cmd_archive NVARCHAR(MAX) NULL
    , cmd_restore NVARCHAR(MAX) NULL 
);

SELECT 
    @host = LOWER(SUBSTRING(name,0,CHARINDEX('\',name)))
    ,@dst_schema = LOWER(CONCAT(SUBSTRING(name, 3, 6),'_dbo'))
FROM sys.[servers] AS [s]
WHERE [s].[server_id]=0;

SELECT @fqdn = CONCAT(@host,'.centralinfra.net,1433');

DECLARE csr_articles CURSOR FAST_FORWARD READ_ONLY FOR 
    SELECT CONCAT([ms].[source_owner],'.',[ms].[source_object]) AS tbl, [ms].[source_object] AS tbl_only
    FROM [dbo].[MSarticles] AS [ms]
    WHERE [ms].[publisher_db]='Arizona'

OPEN csr_articles

FETCH NEXT FROM csr_articles INTO @tbl_full, @tbl_only

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @tbl_file = REPLACE(@tbl_full,'.','_');
    INSERT INTO @cmd (
        [fqdn],
        [tbl_name],
        [cmd_archive],
        [cmd_restore]
    )
    SELECT 
        @fqdn-- fqdn
        ,@tbl_full-- tbl_name
        ,REPLACE(REPLACE(REPLACE(REPLACE(@tpl_archive, '@tbl@', @tbl_full),'@tbl_file@',@tbl_file),'@host@',@host),'@fqdn@', @fqdn)
        ,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@tpl_rest, '@tbl@', @tbl_only),'@tbl_file@',@tbl_file),'@host@',@host),'@hyperscale@', @hyperscale), '@dst@',@dst_schema)
    ;
     

    FETCH NEXT FROM csr_articles INTO @tbl_full, @tbl_only
END

CLOSE csr_articles
DEALLOCATE csr_articles

SELECT * 
FROM @cmd AS [c]