Copying data from remote server to local with dynamic sql, must set identity_insert

I realize the dynamic is often not the ideal solution, but I am against the wall on this.  I must get it done and begin testing  this weekend, so I would be incredibly grateful for Expert assistance.  Maybe my approach isn’t the best, so I want to say that I also welcome other suggestions.  Basically, these are the three actions I need to perform, and I am using dynamic because of the server/ database calls, both locally and across a linked server.

NOTE:  The servers are linked
             The source and target tables will always be the same name/definition
             The identity column values must be preserved and written into the local db/table
1)
Count remote server/ db/ table - @countA
Count local db/table - @countB
IF @countA > @countB,
set identity_insert local db/table on
insert local dB/table (columns…)
select columns…. From remote serve/db/table EXCEPT select columns… from local db/ table
Set @inscount = @@ROWCOUNT —- to be used later in output
Set identity_insert local db/table off

2)
IF @countA < @countB
Delete local db/ table
Set @delcount = @@ROWCOUNT — to be used later in output
set identity_insert local db/table on
insert local dB/table (columns…)
select columns…. From remote serve/db/table
set identity_insert local db/table off
Set @inscount = @@ROWCOUNT — to be used later in output

3)
IF (@countA = @countB and exists
Select columns…. From remote Srv/db
EXCEPT
Select columns…. From local db/table
)
Delete local db/ table
Set @delcount = @@ROWCOUNT — to be used later in output
set identity_insert local db/table on
insert local dB/table (columns…)
select columns…. From remote serve/db/table
set identity_insert local db/table off
Set @inscount = @@ROWCOUNT — to be used later in output


When @debug =1, I would like to output my statement rather than execute it.(it’s nit outputting right now)
When the data transfer is complete, I would like to output the counts of the actions that were performed.  For example, these are the outputs I need from each action that is executed within the dynamic Sql:, possibly with headers so I know which of the thee actions in the single dynamic @statement was performed.

1) The 1st action listed above would output something like this:
“Counts were low in the target db name.
### records inserted from sourcesrv.sourcedb.dbo.tablename into local db/table.”  -< ‘###’ is the numeric count.”

2) The 2nd action listed above would output something like this:
“Counts were low in the source servername.db name.dbo.tablename.
### records deleted from localdb/table
### records reloaded from sourcesrv.dbo.tablename into localdb.dbo.table” —<<< ‘###’is the numeric count

3) The 3rd action listed above would output something like this:
“Counts matched but the data was different.
### records deleted from localdb/table
### records reloaded from sourcesrv.dbo.tablename into localdb.dbo.table —<<< ‘###’ is the numeric count


Please.

Are there any Experts available to assist?

As two of your actions are deleteing the target table, I would not use this flow at all and delete the target tables always. As long as you don't have million rows to transfer..

The next step would be using TRUNCATE TABLE instead of DELETE to speed up the deletion by avoiding extensive logging.

For your coding, use QUOTENAME() instead of hardcodeing the brackets.

Also as you want use a local target database, you need to collect the column names with dynamic SQL. This is an exercise you can do on your own.

CREATE PROCEDURE dbo.p_Extract (
    @SourceServerName SYSNAME,
    @SourceDatabaseName SYSNAME,
    @SourceSchemaName SYSNAME,
    @SourceTableName SYSNAME,
    @TargetSchemaName SYSNAME,
    @TargetTableName SYSNAME,
    @LogEventUID UNIQUEIDENTIFIER = NULL ,
    @DebugBit BIT = 0 ,
    @WhatIfBit BIT = 0 ,
    @VerboseBit BIT = 1
)
AS
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    -- Constants.
    DECLARE @GENERIC_ERROR INT = -1;
    DECLARE @LOG_SOURCE NVARCHAR(255) = OBJECT_NAME(@@PROCID);
    DECLARE @NEW_LINE NVARCHAR(MAX) = NCHAR(13) + NCHAR(10);
    DECLARE @NO_ERROR INT = 0;

    -- Variables.
    DECLARE @Batch NVARCHAR(MAX);
    DECLARE @ColumnList NVARCHAR(MAX);
    DECLARE @MessageText NVARCHAR(MAX) = N'-- ' + DB_NAME() + N'.' + OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID);
    DECLARE @RowCount INT;

    IF ( @DebugBit = 1 )
        SET @VerboseBit = 1;

    IF ( @VerboseBit = 1 )
        PRINT @MessageText;

    BEGIN TRY;
        SET @LogEventUID = ISNULL(@LogEventUID, NEWID());

        SELECT  @ColumnList = STRING_AGG(CONVERT(NVARCHAR(MAX), QUOTENAME(C.name)), ', ') WITHIN GROUP ( ORDER BY C.name )
        FROM    sys.tables T
            INNER JOIN sys.columns C ON C.object_id = T.object_id
        WHERE T.name = @TargetTableName
            AND T.schema_id = SCHEMA_ID(@TargetSchemaName);

        SET @Batch = N'
IF EXISTS(  SELECT  *
            FROM    @SourceServerName.@SourceDatabaseName.sys.tables T
            WHERE   T.name = PARSENAME(''@SourceTableName'', 1)
                AND T.schema_id = SCHEMA_ID(PARSENAME(''@SourceSchemaName'', 1))
    )
BEGIN
    TRUNCATE TABLE @TargetDatabaseName.@TargetSchemaName.@TargetTableName;
    INSERT INTO @TargetDatabaseName.@TargetSchemaName.@TargetTableName ( @ColumnList )
        SELECT  @ColumnList
        FROM    @SourceServerName.@SourceDatabaseName.@SourceSchemaName.@SourceTableName;
        SET @RowCountOut = @@ROWCOUNT;
END;
';

        IF EXISTS(  SELECT  *
                    FROM    sys.tables T
                        INNER JOIN sys.identity_columns IC ON IC.object_id = T.object_id
                    WHERE T.name = @TargetTableName AND T.schema_id = SCHEMA_ID(@TargetSchemaName)
            )
        BEGIN
            SET @Batch = N'SET IDENTITY_INSERT @TargetDatabaseName.@TargetSchemaName.@TargetTableName ON;' + @NEW_LINE +
                @Batch + N'SET IDENTITY_INSERT @TargetDatabaseName.@TargetSchemaName.@TargetTableName OFF;';
        END;

        SET @Batch = REPLACE(@Batch, '@SourceServerName', QUOTENAME(@SourceServerName));
        SET @Batch = REPLACE(@Batch, '@SourceDatabaseName', QUOTENAME(@SourceDatabaseName));
        SET @Batch = REPLACE(@Batch, '@SourceSchemaName', QUOTENAME(@SourceSchemaName));
        SET @Batch = REPLACE(@Batch, '@SourceTableName', QUOTENAME(@SourceTableName));
        SET @Batch = REPLACE(@Batch, '@TargetDatabaseName', QUOTENAME(DB_NAME()));
        SET @Batch = REPLACE(@Batch, '@TargetSchemaName', QUOTENAME(@TargetSchemaName));
        SET @Batch = REPLACE(@Batch, '@TargetTableName', QUOTENAME(@TargetTableName));
        SET @Batch = REPLACE(@Batch, '@ColumnList', @ColumnList);

        IF ( @DebugBit = 1 )
            PRINT @Batch;

        IF ( @WhatIfBit = 0 )
        BEGIN
            EXECUTE sys.sp_executesql @Batch ,
                                      N'@RowCountOut INT OUTPUT' ,
                                      @RowCountOut = @RowCount OUTPUT;

            IF ( @VerboseBit = 1 )
            BEGIN
                SET @MessageText = N'Extracted ' + FORMAT(ISNULL(@RowCount, -1), '#,##0') + N' rows.';
                PRINT @MessageText;
            END;
        END
        ELSE
        BEGIN
            IF ( @VerboseBit = 1 )
                PRINT 'WhatIf mode.';
        END;

        RETURN @NO_ERROR;
    END TRY
    BEGIN CATCH
        IF ( @@trancount > 0 )
            ROLLBACK TRANSACTION;
        /*
        See Erland Somarskog's error handling article.
        EXECUTE dbo.p_ThrowError @LogEventUID ,
                                 @LOG_SOURCE;
        */
        RETURN @GENERIC_ERROR;
    END CATCH;

Select allOpen in new window

As already mentioned, the procedure must be created in the target database. Otherwise you need some more dynamic SQL.

The @LogEventUID parameter is used in my systems to create grouped log entries.
The missing indention for the actual batch code is intentional.

This is great.  Thank you both very much.  I am beginning to test now.

snarf, I had to change this:
        SELECT @statement = 'IF(SELECT COUNT(*) FROM '+@sourcesrv +'.'+@sourcedb+'.'+@schema+'.'+@table+''')<>
         (SELECT COUNT(*) FROM '+@targetdb +'.'+@targetschema+'.'+@targettable+''')

to this:

         SELECT @statement = 'IF(SELECT COUNT(*) FROM '+@sourcesrv +'.'+@sourcedb+'.'+@schema+'.'+@table+') > 
         (SELECT COUNT(*) FROM '+@targetdb +'.'+@targetschema+'.'+@targettable+')


Those 3 ticks on the far right were producing a single extra tick to the right of both the @table and @targettable.

I also changed the IF @debug = 1 SELECT  @statement to a PRINT @statement, because that output the statement fully for my visual.  The SELECT that you had in your suggestion just returned it all in a single grid line.


ste5an, you're absolutely right about the TRUNCATE vs DELETE.  I don't know why I did not think about that.  Of course, using the TRUNCATE means I cannot get a @@ROWCOUNT, but I'm not going to worry about that.  

--For your coding, use QUOTENAME() instead of hardcodeing the brackets.
I put the brackets in to avoid problems with server names that had spaces or named instances.  I wasn't aware that I could get around that with QUOTENAME().  Thank you.  I will test that.  I think I may have some other questions regarding your constants and variables, but I want to finish testing snarf's suggestion and then I will test yours.

Will have an update soon.

Hello snarf0001.  I've tested your version extensively and everything tests well except for two things.  I've outlined my test end to end and provided all code so that you can replicate it on your end, if you have time.  The failures were with #3 and #5.  Please see details below, the code and my table screenshot, and let me know what you think.  

ste5an, I am going to test yours now.

-- attached customer table with identity column on both source and local servers.  start with both tables empty
  
-- 1. insert these 12 records into source server/db/table, then run statement.  all 12 are copied into target correctly.
insert sourceServer.dba.dbo.customer(firstname,lastname,phonenumber,emailaddress,priority)
values
('Daniel','Craig','123-456-7899','dcraig@gmail.com',1),
('James','Bond','482-666-1266','jbond@yahoo.com',1),
('Julia','Childs','555-555-5555','jchilds@yahoo.com',1),
('Jane','Pane','415-223-1224','janepane@gmail.com',2),
('George','Clooney','415-223-7895','gclooney@gmail.com',2),
('Ralph','Lauren','312-656-3374','rlauren@gmail.com',1),
('Calvin','Klein','312-656-3374','cK@gmail.com',1),
('Kate','Spade','312-656-3374','Kspade@gmail.com',1),
('Bob','Dole','312-656-3374','bdole@gmail.com',2),
('Ronald','Reagan','312-656-3374','ronaldreagan@gmail.com',1),
('Al','Pacino','123-336-9994','apacino@yahoo.com',2),
('Pat','Benatar','312-656-3374','patbenatar@gmail.com',1);

--2. Insert 1 new record into source table, then run statement.  new record is copied from source to target correctly
insert sourceServer.dba.dbo.customer(firstname,lastname,phonenumber,emailaddress,priority)
values('Tom','Hardy','888-777-3333','ohyeah@hardy.com',1);

--3. Insert 1 new record into target table.  source is less than target, target should be truncated/reloaded.  No changes occur.
-- Reviewed code, changed not equal (<> ) to less than (< ), ran again.  Target was truncated/reloaded correctly. Counts and data match. 
insert dba.dbo.customer(firstname,lastname,phonenumber,emailaddress,priority)
values('Tom','Hanks','354-266-8925','thanks@gmail.com',1);
  
--4. Updated a few attributes in source so counts match but data is different, ran statement.  Target was truncated/reloaded correctly.
update sourceServer.dba.dbo.customer set priority = 1 where customerid = 9
update sourceServer.dba.dbo.customer set emailaddress = 'katespade@katespade.com' where customerid = 8
update sourceServer.dba.dbo.customer set phonenumber = '714-882-6626' where customerid = 4

--5. Insert 1 new record in source and update another record so it is different than in target.  One new record AND one changed record
-- Ran statement, only 2 rows affected.  The new insert was copied from source to target correctly.  The record that I updated was also written to the target as a new record -- not intended -- and somehow we got a duplicate identity value for both of these records (see screenshot).
insert sourceServer.dba.dbo.customer (firstname,lastname,phonenumber,emailaddress,priority)
values('Isaac','Newton','111-222-3333','inewton@att.com',1);
update sourceServer.dba.dbo.customer set phonenumber = '442-112-7878' where customerid = 10


Solution -- Is my only option to use a if source count <> target count, just truncate and reload?  Which means I'm doing the same thing for all three cases? 

Select allOpen in new window

This is the statement as I am running it:

DECLARE
 @sourcesrv VARCHAR(100) = 'MyOtherServer',
 @sourcedb VARCHAR(100) = 'DBA',
 @schema VARCHAR(100) = 'dbo',
 @table VARCHAR(100) = 'customer',
 @targetdb VARCHAR(100) = 'DBA',
 @targetschema VARCHAR(100) = 'dbo',
 @targettable VARCHAR(100) = 'customer',
 @debug BIT = 0;


   -- local variables
   DECLARE 
      @statement NVARCHAR(MAX),  -- full statement
      @identity BIT = 0,        -- does table have IDENTITY column
      @cols VARCHAR(MAX),       -- get column list for @table 
      @inscount VARCHAR,        -- capture insert count for output
      @delcount VARCHAR;        -- capture delete count for output

      -- does @table have identity column
      IF EXISTS(
         SELECT 1 FROM sys.tables a JOIN sys.identity_columns b 
           ON a.object_id = b.object_id
         WHERE a.name = @table
         )
      SET @identity = 1

      -- populate @cols  
      SET @cols = (SELECT SUBSTRING(
         (SELECT ', ' + QUOTENAME(COLUMN_NAME)
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = @table
            ORDER BY ORDINAL_POSITION
            FOR XML path('')),
         3,
         200000));


      -- compare source and target counts for 3 cases:
      IF(@identity = 1)
      BEGIN
         SELECT @statement = 'IF(SELECT COUNT(*) FROM '+@sourcesrv +'.'+@sourcedb+'.'+@schema+'.'+@table+') > (SELECT COUNT(*) FROM '+@targetdb +'.'+@targetschema+'.'+@targettable+') 
         BEGIN
            SET IDENTITY_INSERT ['+@targetschema+'].['+@targettable+'] ON; 
            INSERT ['+@targetdb+'].['+@targetschema+'].['+@targettable+'] (' + @cols + N')
            SELECT ' + @cols + N'
            FROM ['+@sourcesrv+'].['+@sourcedb+'].['+@schema+'].['+@table+'] 
            EXCEPT
            SELECT ' + @cols + N'
            FROM ['+@targetdb+'].['+@targetschema+'].['+@targettable+']
            SET @inscount = CAST(@@ROWCOUNT AS VARCHAR)
            SET IDENTITY_INSERT ['+@targetschema+'].['+@targettable+'] OFF;
         END
         ELSE
         IF(SELECT COUNT(*) FROM '+@sourcesrv +'.'+@sourcedb+'.'+@schema+'.'+@table+')  < (SELECT COUNT(*) FROM '+@targetdb +'.'+@targetschema+'.'+@targettable+') 
         BEGIN
            TRUNCATE TABLE ['+@targetschema+'].['+@targettable+']
            SET IDENTITY_INSERT ['+@targetschema+'].['+@targettable+'] ON; 
            INSERT ['+@targetdb+'].['+@targetschema+'].['+@targettable+'] (' + @cols + N')
            SELECT ' + @cols + N'
            FROM ['+@sourcesrv+'].['+@sourcedb+'].['+@schema+'].['+@table+'] 
            SET @inscount = CAST(@@ROWCOUNT AS VARCHAR)
            SET IDENTITY_INSERT ['+@targetschema+'].['+@targettable+'] OFF;
         END
         ELSE
         IF(SELECT COUNT(*) FROM '+@sourcesrv +'.'+@sourcedb+'.'+@schema+'.'+@table+') = (SELECT COUNT(*) FROM '+@targetdb +'.'+@targetschema+'.'+@targettable+') 
         AND EXISTS
            (SELECT ' + @cols + N'
            FROM ['+@sourcesrv+'].['+@sourcedb+'].['+@schema+'].['+@table+'] 
            EXCEPT
            SELECT ' + @cols + N'
            FROM ['+@targetdb+'].['+@targetschema+'].['+@targettable+'])
         BEGIN
            TRUNCATE TABLE ['+@targetschema+'].['+@targettable+']
            SET IDENTITY_INSERT ['+@targetschema+'].['+@targettable+'] ON; 
            INSERT ['+@targetdb+'].['+@targetschema+'].['+@targettable+'] (' + @cols + N')
            SELECT ' + @cols + N'
            FROM ['+@sourcesrv+'].['+@sourcedb+'].['+@schema+'].['+@table+'] 
            SET @inscount = CAST(@@ROWCOUNT AS VARCHAR)
            SET IDENTITY_INSERT ['+@targetschema+'].['+@targettable+'] OFF;
         END   '
      END

	IF(@debug = 1)
    BEGIN
		PRINT @statement
    END
    ELSE
		EXEC sp_executesql @statement, N'@inscount int out', @inscount OUT

Select allOpen in new window

You can see this is for #3 above which resulted in duplicate identity values in my local table.  In this test I inserted a new record to source AND updated an existing one:

ste5an, can you expand upon the @WhatIfBit and @VerboseBit just a bit?  No pun intended.  :)  I just am looking for details regarding the usage.  Also your usage of XACT_ABORT, please.

I borrowed WhatIf from PowerShell, thus when setting it to 1, a procedure does not change the system.
And Verbose is the same as in PS or other command line tools. Set it to 1 to spill out some messages.
Debug set to 1 normally spills out intermediate rowsets to show its internal state. Here it prints the created T-SQL batch.

And finally: Never be afraid to run code and explore its parameters in your test system. Cause you have, of course, a backup.

I said at the beginning of this post that I have a large procedure which I use to copy data from one server/db/table to a local db/table.  It is a modified version of this one:  https://www.sqlservercentral.com/articles/dynamic-sql-merge-1  

The procedure in that link uses MERGE, and it is very helpful -- except that it doesn't work on tables with IDENTITY columns.  That is the reason for this change.  I've revised it to have two different workflows -- one for tables without identity columns using MERGE, and the other for tables with IDENTITY columns using IDENTITY_INSERT.

snarf0001, you can see I've gotten your suggestion into the attached, beginning on line 228.  I have tested it successfully with the three test cases I mentioned before.  The problem I had in test #5 is still there, but I haven't decided yet how to handle that one.  

The attached works well except that I cannot output my action counts.  You can see I've commented out the OUTPUT $action section on line #374.  This is because it outputs a line for every row acted upon -- insert, update, delete -- and I am moving a lot of data.  I don't want to output a thousand lines.  I need to output a simple count of what was done.  

For the MERGE path, it should be this:   tablename - # records inserted, # records updated, # records deleted
For the IDENTITY_INSERT, it should be this:   tablename - # records inserted

Do either of you have any suggestions?  Right now it just returns 'commands completed successfully', and I really need to output the action counts.  

ste5an, I am still working on testing your suggestion, but I have not been able to call it from within the attached procedure yet.  Can you suggest an approach?
procedurename.sql

First of all: Please post correct T-SQL. Your sp has some errors.

Some code comments (top-down):

- Use meaningful variable and parameter names. Then commenting declarations is not necessary. It also means easier understanding code.
- Use Camel-Case for object names to increase readability.
- Use domain specific data types when possible. The correct data type for objects is SYSNAME.
- My default SP code is omitting the out-most BEGIN..END, but uses an additional SET and uses standard error handling according to Erland Somarskog. See my code in my other post.
- User DROP TABLE IF EXISTS, when on SQL Server 2016+ to avoid the IF OBJECT_ID() idiom.
- Create your temporary tables with PRIMARY KEY constratints, but at least with UNIQUE constraints for the keys. You never now when this will save your back.
- Always derive table alias names from the table name.
- Always query sys.tables with schema.
- Favor closed expressions over IF..SET. E.g.

DECLARE @TargetSchemaName sysname;
DECLARE @TargetTableName sysname;

DECLARE @HasIdentity BIT = (
            SELECT      COUNT(*)
            FROM        sys.tables T
                JOIN    sys.identity_columns IC ON IC.object_id = T.object_id
            WHERE       T.schema_id = SCHEMA_ID(@TargetSchemaName)
                        AND T.name = @TargetTableName
        );

SELECT  @HasIdentity;

/* Instead of
IF EXISTS
(
    SELECT 1
    FROM sys.tables a
        JOIN sys.identity_columns b ON a.object_id = b.object_id
    WHERE a.name = @table
)
    SET @isIDENTITY = 1;
*/
*/

Select allOpen in new window

- When using XML for list building use STUFF() instead of SUBSTRING().
- Use STRING_AGG() instead of XML when on SQL Server 2017+. Otherwise use XML correctly. Your approach without querying the value will escape XML special entities:

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.Test;
CREATE TABLE dbo.Test
(
    [Special>Name] INT NOT NULL
);

DECLARE @YourApproachText NVARCHAR(MAX) = (
            SELECT  STUFF((
                            SELECT      ', ' + QUOTENAME(COLUMN_NAME)
                            FROM        INFORMATION_SCHEMA.COLUMNS
                            WHERE       TABLE_NAME = 'Test'
                            ORDER BY    ORDINAL_POSITION
                            FOR XML PATH('')
                        ), 1, 2, ''
                    )
        );

DECLARE @CorrectApproachText NVARCHAR(MAX) = STUFF((
                                                    SELECT          ', ' + QUOTENAME(C.name)
                                                    FROM            sys.tables T
                                                        INNER JOIN  sys.columns C ON C.object_id = T.object_id
                                                    WHERE           T.schema_id = SCHEMA_ID('dbo')
                                                                    AND T.name = 'Test'
                                                    FOR XML PATH(''), TYPE
                                                ).value('.', 'NVARCHAR(MAX)'), 1, 2, ''
                                            );

SELECT  @YourApproachText AS YourApproachText,
        @CorrectApproachText AS CorrectApproachText;

Select allOpen in new window

- During development, drop the precondition tests. Add they later at the beginning of your sp. Cause your tests are incomplete, some existence tests are missing. And it makes reading and understanding the core cases simpler.
- When assigning parameters or variables, then use the correct syntax. Favor SET @variable = .. over SELECT @variable = ... Cause the SELECT approach may not touch the variable, when no row is found, or it may return an arbitrary value when more rows exists:

USE tempdb;
GO

DECLARE @Test NVARCHAR(255) = N'Some value.';

SELECT  @Test = V.name
FROM    master.dbo.spt_values V
WHERE   V.number = -666;
SELECT  @Test;

SET @Test = (
    SELECT  V.name
    FROM    master.dbo.spt_values V
    WHERE   V.number = -666
);
SELECT  @Test;

SELECT  @Test = V.name
FROM    master.dbo.spt_values V
WHERE   V.number = 0;
SELECT  @Test;

SELECT  V.name
FROM    master.dbo.spt_values V
WHERE   V.number = 0;

Select allOpen in new window

- Either you don't read the source columns or when you do, you need to test for an complete overlap and data type equality.
- Use database, schema and table names consistently. Right now you specify a target schema, but you don't use it everywhere.
- Favor the sys views over INFOMATION_SCHEMA.
- As dynamic SQL has different drawback, favor REPLACE over concatenation. This makes writing and reading of dynamic SQL easier. E.g. from my post above:

SET @Batch =
    N'SET IDENTITY_INSERT @TargetDatabaseName.@TargetSchemaName.@TargetTableName ON;' + @NEW_LINE + @Batch
    + N'SET IDENTITY_INSERT @TargetDatabaseName.@TargetSchemaName.@TargetTableName OFF;';

SET @Batch = REPLACE(@Batch, '@TargetDatabaseName', QUOTENAME(DB_NAME()));
SET @Batch = REPLACE(@Batch, '@TargetSchemaName', QUOTENAME(@TargetSchemaName));
SET @Batch = REPLACE(@Batch, '@TargetTableName', QUOTENAME(@TargetTableName));

-- instead of

SET @Batch =
    N'SET IDENTITY_INSERT ' + QUOTENAME(DB_NAME()) + '.' + QUOTENAME(@TargetSchemaName) + '.' + QUOTENAME(@TargetTableName) + ' ON;' + @NEW_LINE + @Batch
    + N'SET IDENTITY_INSERT ' + QUOTENAME(DB_NAME()) + '.' + QUOTENAME(@TargetSchemaName) + '.' + QUOTENAME(@TargetTableName) + ' OFF;';

Select allOpen in new window

- Inline into your dynamic SQL only what is necessary. E,g. your row tests on source and target can probably be separate queries depending  on the logic behind your approach.
- Having larger blocks of inline comments are a strong indictor to place those blocks into separate methods.
- In large procedures I use my @DebugBit to spill out intermediate rowsets to the grid to show the used data. I use the @VerboseBit to spill out some messages. The @VerboseBit is normally used for embedded sproc calls, which are known to be correct and the I suppress their output.
- And last, but not least:

What is your actual use-case?
What is your rational behind reinventing the wheel?
About how many tables and rows to sync in what frequency are we talking?

Such a task is better done in SSIS, or with SSDT or external tools like Redgate SQL Data Compare or even PowerShell or your own, custom .NET command line utility.

This should be evaluated, cause the MERGE approach with another server instance has some limitations for example on tables without PK and some exotic or deprecated data types or incompatible collations.

The use case is to ‘synchronize’ tables on demand.  Won’t always be the same source server, database or table, won’t always be the same target servers/db/table.  My previous edition using MERGE was very effective for the business need except that it did not support tables with identity columns.  SSIS/SSDT were considered early on but that was rejected.  There is no more time to build or test different methods.  I am not reinventing the wheel.  I simply must revise the single MERGE procedure that has already proven itself to also support MERGE on tables without identity columns as well as tables with identity columns.  The last procedure that I attached worked on both.  I tested it numerous times both ways yesterday.  The only thing I have not resolved is the output of the rowcount for completed actions.  If I uncomment the currently commented out section on line 374, that ‘works’ only for the MERGE statements, and it outputs one line for every records acted on.  That could be thousands of records.  I don’t want that.  I just want this:


For the MERGE path, it should be this:   tablename - # records inserted, # records updated, # records deleted
For the IDENTITY_INSERT, it should be this:   tablename - # records inserted

Well, the Redgate tools can be automated..

And for the row count: Using MERGE, the row count can report a completely wrong number. Especially in your general case. Here the only generic and reliable solution is to use the OUTPUT clause and evaluate the results,.

Thank you, ste5an, but I don't have the time to research redgate.  I must get this procedure function today if possible.  The mege works, the identity_insert works. I just need tou output the counts of the actionsn performed.  I've read a number of examples for doing this with merge, like these:
  https://www.purplefrogsystems.com/blog/2019/11/capturing-insert-and-update-counts-from-merge/
  https://sqlwithmanoj.com/2017/03/29/get-row-count-of-insertupdatedelete-records-in-merge-statement-msdn-tsql-forum/
  https://stackoverflow.com/questions/1268491/sql-server-2008-merge-best-way-to-get-counts

I am going to try to test those now, but I am not certain yet why my non-MERGE @inscounts are not being output with those actions. Do you see it?

As you may have deduced from my code comments, the code is, well, suboptimal to spot such things. For spotting it, you need to clean it up. Otherwise the only thing you can do is to debug the final generated state´ment. Consider posting it.

To tackle such a problem, my first step would be separating the different execution branches into separate methods. Smaller methodes are simpler to maintain and also to extend in your case.

btw, as further hack doesn't hurt anymore, go the easy way. SET IDENTITY_INSERT is a global setting. Thus restore your old procedure. Create a wrapper procedure. Let it turn IDENTITY_INSERT, if needed. Then let it call the old procedure. Finally do the clean up.

Sub-optimal or not, and even even I were to do a wrapper procedure, I still need to output the count of the actions performed.  This is what I am trying to do now.  Let's assume I use the procedure I posted here:  https://www.experts-exchange.com/questions/29230185/Copying-data-from-remote-server-to-local-with-dynamic-sql-must-set-identity-insert.html?anchorAnswerId=43371317#a43371317

How do I output the insert count?

As I already tried to point out, your unnecessary mixed-in conditonal flows make reading the procedure unnecessarly hard. Without a test sytem, too hard. Especially as I don't understand why they are there in the first place.

My approach would writing a concrete MERGE in the test envorinment enclosed by a BEGIN TRANSECTION..ROLLBACK TRANSACTION to get reproducable results.
Then I would embed this working statement into a dynamic SQL. Create the temporary output table you will use in the dynamic SQL statement. before running the statement  Then you should get your output table and can retrieve your row counts from it.
Finally insert placeholders for the dynamice parts, tables and columns etc. Now you calculate these values and inject them by using REPLACE.