Merge SQL Databases

1. IdentifyMaxIdentity.sql

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

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
  )
  
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)

2. IncrementIdentities.sql

use SourceDB;
go;
 
declare @Increment bigint
set @Increment = 6000 -- value from step 3
 
declare @TablesToImport table
(
  [Name] nvarchar(128)
)
 
insert into @TablesToImport (Name) values
  ('Users'), ('Orders'), ('Products'), ('Categories')
     
declare @FkToIncrement table
(
  [Schema] nvarchar(128),
  [Table] nvarchar(128),
  [Column] nvarchar(128)
)
 
-- SCAN SYSTEM TABLES
insert into @FkToIncrement ([Schema], [Table], [Column])
select
  sc.TABLE_SCHEMA,
  t.name [table], 
  c.name ForeignKeyColumn 
from sys.foreign_key_columns as fk
  inner join sys.tables as t 
    on fk.parent_object_id = t.object_id
  inner join sys.columns as c 
    on fk.parent_object_id = c.object_id 
    and fk.parent_column_id = c.column_id
  inner join INFORMATION_SCHEMA.COLUMNS sc
    on sc.TABLE_NAME = t.name
    and sc.COLUMN_NAME = c.name
where fk.referenced_object_id in
  (
    select object_id 
    from sys.tables 
    where name in
    (
      select [Name] 
      from @TablesToImport
    )
  )
  and t.name in
  (
    select [Name] 
      from @TablesToImport
  )

-- REMOVE ALL FULLTEXT INDEXES
declare @dropFtSql nvarchar(max)
set @dropFtSql = ''
 
select @dropFtSql = @dropFtSql 
  + 'drop fulltext index on ' + t.name + ';'
  from sys.tables t 
  inner join sys.fulltext_indexes fi 
    on t.[object_id] = fi.[object_id] 
 
if (@dropFtSql <> '')
begin
  execute (@dropFtSql)
end  
print 'all fulltext indexes are removed'
 
-- REMOVE ALL FOREIGN KEYS
declare @removeFkSql nvarchar(MAX) 
set @removeFkSql = ''
  
select @removeFkSql = @removeFkSql + 'alter table '
  + QUOTENAME(CONSTRAINT_SCHEMA) + '.'
  + QUOTENAME(TABLE_NAME) + ' drop constraint '
  + QUOTENAME(CONSTRAINT_NAME) + '; '
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  where CONSTRAINT_TYPE = 'FOREIGN KEY'
 
if (@removeFkSql <> '')
begin
  execute(@removeFkSql)
end
print 'all foreign keys are removed'
 
-- REMOVE ALL PRIMARY KEYS
declare @removePkSql nvarchar(MAX) 
set @removePkSql = ''
 
select @removePkSql = @removePkSql + 'alter table '
  + QUOTENAME(CONSTRAINT_SCHEMA) + '.'
  + QUOTENAME(TABLE_NAME) + ' drop constraint '
  + QUOTENAME(CONSTRAINT_NAME) + '; '
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  where CONSTRAINT_TYPE = 'PRIMARY KEY'
 
if (@removePkSql <> '')
begin
  execute(@removePkSql)
end
print 'all primary keys are removed'

declare @Schema nvarchar(128),
  @Table NVARCHAR(128),
  @Column NVARCHAR(128),
  @DataType NVARCHAR(128);
             
declare FkCursor cursor for
  select * from @FkToIncrement
 
open FkCursor
 
fetch next from FkCursor 
  into @Schema, @Table, @Column
while @@FETCH_STATUS = 0
begin
  execute('update ['+@Schema+'].['+@Table+'] 
    set ['+@Column+'] = ['+@Column+'] + ' + @Increment)
  fetch next from FkCursor into @Schema, @Table, @Column
end
 
close FkCursor
deallocate FkCursor
 
print 'all foreign keys are incremented'

declare @IdentityToIncrement table
(
  [Schema] nvarchar(128),
  [Table] nvarchar(128),
  [Column] nvarchar(128),
  [DataType] nvarchar(128)
)
 
-- SCAN SYSTEM TABLES
insert into @IdentityToIncrement 
  (
    [Schema], 
    [Table], 
    [Column], 
    [DataType]
  )
  select
    TABLE_SCHEMA, 
    TABLE_NAME, 
    COLUMN_NAME, 
    DATA_TYPE
  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
    )
 
-- INCREMENT IDENTITY COLUMNS       
declare IdentityCursor cursor for
  select * from @IdentityToIncrement
 
open IdentityCursor
 
fetch next from IdentityCursor 
  into @Schema, @Table, @Column, @DataType
 
while @@FETCH_STATUS = 0
begin
  execute('alter table ['+@Schema+'].['+@Table+'] add '+@Column+'_Copy ' + @DataType + '')
  execute('update ['+@Schema+'].['+@Table+'] set '+@Column+'_Copy = '+@Column+'')
  execute('alter table ['+@Schema+'].['+@Table+'] drop column '+@Column+'')
  execute('exec sp_rename ''['+@Schema+'].['+@Table+'].'+@Column+'_Copy'', '''+@Column+''', ''COLUMN''')
  execute('update ['+@Schema+'].['+@Table+'] set ['+@Column+'] = ['+@Column+'] + ' + @Increment)
  execute('alter table ['+@Schema+'].['+@Table+'] alter column ['+@Column+'] ' + @DataType + ' not null')
  fetch next from IdentityCursor into @Schema, @Table, @Column, @DataType
end
 
close IdentityCursor
deallocate IdentityCursor
 
print 'all identity columns are incremented'

3. Merge.sql

-- MERGE Categories
set identity_insert TargetDB.dbo.Categories on
insert into TargetDB.dbo.Categories ([Id], [Name])
select [Id],[Name]
  from SourceDB.dbo.Categories
set identity_insert TargetDB.dbo.Categories off
 
-- MERGE Products
set identity_insert TargetDB.dbo.Products on
insert into TargetDB.dbo.Products ([Id], [Name], [CategoryId])
select [Id],[Name],[CategoryId]
  from SourceDB.dbo.Products
set identity_insert TargetDB.dbo.Products off