Skip to content

Instantly share code, notes, and snippets.

@lAnubisl
Last active September 15, 2022 03:09
Show Gist options
  • Save lAnubisl/c7656fecd4db571e5ec57d1a7ce8bac1 to your computer and use it in GitHub Desktop.
Save lAnubisl/c7656fecd4db571e5ec57d1a7ce8bac1 to your computer and use it in GitHub Desktop.
Merge SQL Databases
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)
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'
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment