Imagine you have 2 databases with identical schema. These databases were working for different application instances for some time. And now you need to merge them together for some reason.

You can use different tools to achieve this goal. For example dbForge Data Compare or SQL Data Compare. But these tools cost money and if you don't merge databases every day this is probably not an option for you. Also these tools does not know full specific of your database structure including unique indexes, check constraints and triggers.

Another big deal is identity columns that are using as primary keys. For two databases these keys can be same but represent different entities.

In my practice I face database merge task second time and here is how I handle it.

Step 1. Identify tables that must be merged.
If you need to merge only several tables of hundred you probably don't want to have a deal with full database schema. You also definitely want to skip lookup tables such as "AddressType" it it holds only records like "1 | Home" and "2 | Business".

use TargetDB;
go
 
declare @TablesToImport table
(
  Name nvarchar(128)
)
 
insert into @TablesToImport (Name) values
  ('Users'), ('Orders'), ('Products'), ('Categories')

Step 2. Get information about identity columns.
Identity columns are important. Usually they are used as Primary Keys for table rows. But if two databases were working for different application instances you can think that even if identity primary keys are the same for User table for example they represent different entities and there should be no duplicate records. In this case you need to modify all identity primary keys in source database to make data graphs non-overlapped. But first, you need to identify these identity columns.

declare @TableInformation table
(
  [Schema] nvarchar(128),
  Name nvarchar(128),
  IdentityColumn nvarchar(128),
  MaxIdentity bigint
)
 
 
-- SCAN SYSTEM TABLES
insert into @TableInformation 
(
  [Schema], 
  [Name], 
  [IdentityColumn]
) select
    TABLE_SCHEMA, 
    TABLE_NAME, 
    COLUMN_NAME
  from INFORMATION_SCHEMA.COLUMNS
  where COLUMNPROPERTY(
    OBJECT_ID(TABLE_NAME), 
    COLUMN_NAME, 
    'IsIdentity') = 1
  and OBJECTPROPERTY(
    OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), 
    'IsTable') = 1
  and TABLE_NAME in
  (
    select [Name] 
    from @TablesToImport
  )

Step 3. Calculate max identity value.
Now you have Table that contain all identity column information. Next you beed to find the greatedt identity value for all tables and calculate max value among all of your tables

declare @Schema nvarchar(128),
  @Name NVARCHAR(128),
  @IdentityColumn NVARCHAR(128);
 
declare TablesCursor cursor for
  select [Schema], [Name], [IdentityColumn] 
  from @TableInformation
     
open TablesCursor
 
fetch next from TablesCursor 
  into @Schema, @Name, @IdentityColumn;
 
while @@FETCH_STATUS = 0
begin
  declare @Select nvarchar (max),
    @ParmDefinition nvarchar(500),
    @Result bigint;
 
  -- GENERATE DYNAMIC SQL STATEMENT 
  set @Select = N'select @Result = 
    max(' + @IdentityColumn + ') from ' + @Schema + '.' + @Name
  set @ParmDefinition = N'@Result bigint output';
   
  -- EXECUTE DYNAMIC SQL STATEMENT
  exec sp_executesql @Select, @ParmDefinition, @Result = @Result output;
   
  -- UPDATE MaxIdentity for Identity Column
    update @TableInformation 
      set MaxIdentity = @Result 
      where [Schema] = @Schema
      and [Name] = @Name
      and [IdentityColumn] = @IdentityColumn
 
  fetch next from TablesCursor 
    into @Schema, @Name, @IdentityColumn;
end
 
close TablesCursor
deallocate TablesCursor
 
-- DISPLAY TableInformation
select * from @TableInformation
 
-- DISPLAY MaxIdentity
select MAX(MaxIdentity) from @TableInformation

https://byalexblog.net/merge-sql-databases