Table of Contents [Hide/Show]
See Also Overview Purpose Assumptions The Process The Migration Script Walk-Through Step 1 — Create Linked Servers Step 2 — Create the MIGRATION Database Step 3 — Create the MIGRATION Database Objects Step 4 — Initialize the MIGRATION Database Step 5 — Map the Tables and Columns Step 6 — Generate the Migration Script
Migration
CREATE DATABASE
use Migration --================================================================================================= if exists (select 2 from sys.objects where name = 'SourceSchemas') drop synonym SourceSchemas go create synonym SourceSchemas for [MySourceInstance].MySourceDb.sys.schemas go --================================================================================================= if exists (select 2 from sys.objects where name = 'SourceTables') drop synonym SourceTables go create synonym SourceTables for [MySourceInstance]MySourceDb.sys.tables go --================================================================================================= if exists (select 2 from sys.objects where name = 'SourceColumns') drop synonym SourceColumns go create synonym SourceColumns for [MySourceInstance]MySourceDb.sys.columns go --================================================================================================= if exists (select 2 from sys.objects where name = 'TargetSchemas') drop synonym TargetSchemas go create synonym TargetSchemas for [MyTargetInstance].MyTargetDb.sys.schemas go --================================================================================================= if exists (select 2 from sys.objects where name = 'TargetTables') drop synonym TargetTables go create synonym TargetTables for [MyTargetInstance].MyTargetDb.sys.tables go --================================================================================================= if exists (select 2 from sys.objects where name = 'TargetColumns') drop synonym TargetColumns go create synonym TargetColumns for [MyTargetInstance].MyTargetDb.sys.columns go --================================================================================================= if exists (select 2 from sys.objects where name = 'TargetForeignKeys') drop synonym TargetForeignKeys go create synonym TargetForeignKeys for [MyTargetInstance].MyTargetDb.dbo.sysforeignkeys go --================================================================================================= /****** Object: Table [dbo].[DbMaster] Script Date: 05/11/2010 12:49:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DbMaster]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[DbMaster]( [DbId] [int] IDENTITY(1,1) NOT NULL, [DbName] [varchar](50) NOT NULL, [SourceId] [int] NULL, CONSTRAINT [PK_DbMaster] PRIMARY KEY CLUSTERED ( [DbId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_PADDING OFF GO --================================================================================================= /****** Object: Table [dbo].[TblMaster] Script Date: 05/11/2010 12:49:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TblMaster]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[TblMaster]( [TblId] [int] IDENTITY(1,1) NOT NULL, [TblName] [varchar](50) NOT NULL, [DbId] [int] NOT NULL, [SourceId] [int] NULL, CONSTRAINT [PK_TblMaster] PRIMARY KEY CLUSTERED ( [TblId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_PADDING OFF GO --================================================================================================= /****** Object: Table [dbo].[ColMaster] Script Date: 05/11/2010 12:49:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ColMaster]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[ColMaster]( [ColId] [int] IDENTITY(1,1) NOT NULL, [ColName] [varchar](50) NOT NULL, [TblId] [int] NOT NULL, [SourceId] [int] NULL, CONSTRAINT [PK_ColMaster] PRIMARY KEY CLUSTERED ( [ColId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_PADDING OFF GO --================================================================================================= /****** Object: View [dbo].[ColumnsExt] Script Date: 05/11/2010 12:49:46 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ColumnsExt]')) EXEC dbo.sp_executesql @statement = N'CREATE view [dbo].[ColumnsExt] as with SysTgtColumns as ( select TblName = t.name ,ColName = c.name ,SchemaName = s.name ,ColId = c.column_id ,ColIsIdentity = convert(int, c.is_identity) from dbo.TargetTables t inner join dbo.TargetColumns c on t.object_id = c.object_id inner join dbo.TargetSchemas s on t.schema_id = s.schema_id ) ,a as ( select d.DbId ,d.DbName ,DbSourceId = d.SourceId ,t.TblId ,t.TblName ,TblSourceId = t.SourceId ,c.ColId ,c.ColName ,ColSourceId = c.SourceId ,SchemaName = ''dbo'' ,TblFullName = ''['' + d.DbName + ''].[dbo].['' + t.TblName + '']'' from dbo.ColMaster c inner join dbo.TblMaster t on c.TblId = t.TblId inner join dbo.DbMaster d on t.DbId = d.DbId ) ,MyData as ( select a.* ,SysColId = c.ColId ,c.ColIsIdentity from a left join SysTgtColumns c on a.SchemaName = c.SchemaName and a.TblName = c.TblName and a.ColName = c.ColName ) ,Fkeys as ( select distinct OnTable = OnTable.name ,AgainstTable = AgainstTable.name from dbo.TargetForeignKeys fk inner join dbo.TargetTables onTable on fk.fkeyid = onTable.object_id inner join dbo.TargetTables againstTable on fk.rkeyid = againstTable.object_id where 1=1 -- ignore self joins; they cause an infinite recursion and OnTable.Name <> AgainstTable.Name ) ,FkData as ( select OnTable = o.name ,OnSchema = s.name ,AgainstTable = FKeys.againstTable from dbo.TargetTables o inner join dbo.TargetSchemas s on o.schema_id = s.schema_id left join FKeys on o.name = FKeys.onTable where 1=1 and o.name not like ''sys%'' ) ,FkRecursion as ( -- base case select TableName = OnTable ,SchemaName = OnSchema ,Lvl = 1 from FkData where 1=1 and AgainstTable is null -- recursive case union all select TableName = OnTable ,SchemaName = OnSchema ,Lvl = r.Lvl + 1 from FkData d inner join FkRecursion r on d.AgainstTable = r.TableName ) ,TableLevels as ( select Lvl = max(Lvl) --DESC = delete order; ASC = INSERT order ,TableName ,SchemaName from FkRecursion group by TableName ,SchemaName ) select STblFullName = s.TblFullName ,SColName = s.ColName ,TTblFullName = t.TblFullName ,TColName = t.ColName ,RowNumber = ROW_NUMBER() over (partition by t.TblFullName order by t.SysColId) ,TblHasIdentity = sum(t.ColIsIdentity) over (partition by t.TblFullName) ,Lvl = tl.Lvl from MyData t inner join MyData s on t.ColSourceId = s.ColId inner join TableLevels tl on t.TblName = tl.TableName and t.SchemaName = tl.SchemaName ' GO --================================================================================================= /****** Object: ForeignKey [FK_ColMaster_TblMaster] Script Date: 05/11/2010 12:49:44 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ColMaster_TblMaster]') AND parent_object_id = OBJECT_ID(N'[dbo].[ColMaster]')) ALTER TABLE [dbo].[ColMaster] WITH CHECK ADD CONSTRAINT [FK_ColMaster_TblMaster] FOREIGN KEY([TblId]) REFERENCES [dbo].[TblMaster] ([TblId]) GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ColMaster_TblMaster]') AND parent_object_id = OBJECT_ID(N'[dbo].[ColMaster]')) ALTER TABLE [dbo].[ColMaster] CHECK CONSTRAINT [FK_ColMaster_TblMaster] GO --================================================================================================= /****** Object: ForeignKey [FK_DbMaster_DbMaster] Script Date: 05/11/2010 12:49:44 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_DbMaster_DbMaster]') AND parent_object_id = OBJECT_ID(N'[dbo].[DbMaster]')) ALTER TABLE [dbo].[DbMaster] WITH CHECK ADD CONSTRAINT [FK_DbMaster_DbMaster] FOREIGN KEY([DbId]) REFERENCES [dbo].[DbMaster] ([DbId]) GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_DbMaster_DbMaster]') AND parent_object_id = OBJECT_ID(N'[dbo].[DbMaster]')) ALTER TABLE [dbo].[DbMaster] CHECK CONSTRAINT [FK_DbMaster_DbMaster] GO --================================================================================================= /****** Object: ForeignKey [FK_TblMaster_DbMaster] Script Date: 05/11/2010 12:49:44 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TblMaster_DbMaster]') AND parent_object_id = OBJECT_ID(N'[dbo].[TblMaster]')) ALTER TABLE [dbo].[TblMaster] WITH CHECK ADD CONSTRAINT [FK_TblMaster_DbMaster] FOREIGN KEY([DbId]) REFERENCES [dbo].[DbMaster] ([DbId]) GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_TblMaster_DbMaster]') AND parent_object_id = OBJECT_ID(N'[dbo].[TblMaster]')) ALTER TABLE [dbo].[TblMaster] CHECK CONSTRAINT [FK_TblMaster_DbMaster] GO
use Migration go --================================================================================================= declare @SourceDbId int ,@TargetDbId int ,@SourceDbName varchar(50) ,@TargetDbName varchar(50) select @SourceDbName = 'MySourceDb' ,@TargetDbName = 'MyTargetDb' --================================================================================================= delete from dbo.ColMaster delete from dbo.TblMaster delete from dbo.DbMaster --================================================================================================= insert into dbo.DbMaster (DbName) values (@SourceDbName) set @SourceDbId = @@IDENTITY --================================================================================================= insert into dbo.DbMaster (DbName, SourceId) values (@TargetDbName, @SourceDbId) set @TargetDbId = @@IDENTITY --================================================================================================= insert into dbo.TblMaster ( TblName ,DbId ) select Name ,DbId = @SourceDbId from dbo.SourceTables --================================================================================================= insert into dbo.TblMaster ( TblName ,DbId ) select Name ,DbId = @TargetDbId from dbo.TargetTables --================================================================================================= insert into dbo.ColMaster (ColName, TblId) select c.name ,m.TblId from dbo.SourceTables t inner join dbo.SourceColumns c on t.object_id = c.object_id inner join dbo.TblMaster m on t.name = m.TblName and m.DbId = @SourceDbId --================================================================================================= insert into dbo.ColMaster (ColName, TblId) select c.name ,m.TblId from dbo.TargetTables t inner join dbo.TargetColumns c on t.object_id = c.object_id inner join dbo.TblMaster m on t.name = m.TblName and m.DbId = @TargetDbId
dbo.DbMaster
use migration go declare @SourceDbId int ,@TargetDbId int select @SourceDbId = MySourceId ,@TargetDbId = MyTargetId --================================================================================================= update s set s.SourceId = t.TblId from dbo.TblMaster s inner join dbo.TblMaster t on s.TblName = t.TblName where 1=1 and s.DbId = @SourceDbId and t.DbId = @TargetDbId --================================================================================================= update tc set SourceId = sc.ColId from dbo.ColMaster tc inner join dbo.TblMaster tt on tc.TblId = tt.TblId inner join dbo.ColMaster sc on sc.TblId = tt.SourceId and sc.ColName = tc.ColName
SqlCode
use Migration go with x as ( select distinct TableName = a.TTblFullName ,LevelSort = -a.Lvl ,GroupNum = 10 ,SortOrder = 10 ,SqlCode = 'delete from ' + a.TTblFullName ,Section = 10 from dbo.ColumnsExt a -- Identity Insert = ON union select distinct TableName = a.TTblFullName ,LevelSort = a.Lvl ,GroupNum = 100 ,SortOrder = 2 ,SqlCode = case when a.TblHasIdentity=1 then 'SET IDENTITY_INSERT ' + a.TTblFullName + ' ON' else '-- Table has no identity column' end ,Section = 20 from dbo.ColumnsExt a union select distinct TableName = a.TTblFullName ,LevelSort = a.Lvl ,GroupNum = 100 ,SortOrder = 3 ,SqlCode = 'GO' ,Section = 20 from dbo.ColumnsExt a union select distinct TableName = a.TTblFullName ,LevelSort = a.Lvl ,GroupNum = 100 ,SortOrder = 1 ,SqlCode = '--=================================================================================================' ,Section = 20 from dbo.ColumnsExt a -- INSERT Clause union select distinct TableName = a.TTblFullName ,LevelSort = a.Lvl ,GroupNum = 200 ,SortOrder = 1 ,SqlCode = 'INSERT INTO ' + a.TTblFullName + ' (' ,Section = 20 from dbo.ColumnsExt a -- Target Columns union select TableName = a.TTblFullName ,LevelSort = a.Lvl ,GroupNum = 300 ,SortOrder = a.RowNumber ,SqlCode = ' ' + case when a.RowNumber = 1 then ' ' else ',' end + a.TColName ,Section = 20 from dbo.ColumnsExt a -- SELECT union select distinct TableName = a.TTblFullName ,LevelSort = a.Lvl ,GroupNum = 400 ,SortOrder = 1 ,SqlCode = ') SELECT ' ,Section = 20 from dbo.ColumnsExt a -- Source Columns union select TableName = a.TTblFullName ,LevelSort = a.Lvl ,GroupNum = 500 ,SortOrder = a.RowNumber ,SqlCode = ' ' + case when a.RowNumber = 1 then ' ' else ',' end + a.SColName ,Section = 20 from dbo.ColumnsExt a -- FROM union select distinct TableName = a.TTblFullName ,LevelSort = a.Lvl ,GroupNum = 600 ,SortOrder = 1 ,SqlCode = 'FROM ' + a.STblFullName ,Section = 20 from dbo.ColumnsExt a -- Identity Insert union select distinct TableName = a.TTblFullName ,LevelSort = a.Lvl ,GroupNum = 700 ,SortOrder = 1 ,SqlCode = 'go' ,Section = 20 from dbo.ColumnsExt a -- Identity Insert = OFF union select distinct TableName = a.TTblFullName ,LevelSort = a.Lvl ,GroupNum = 700 ,SortOrder = 2 ,SqlCode = case when a.TblHasIdentity=1 then 'SET IDENTITY_INSERT ' + a.TTblFullName + ' OFF' else '-- Table has no identity column' end ,Section = 20 from dbo.ColumnsExt a -- Identity Insert union select distinct TableName = a.TTblFullName ,LevelSort = a.Lvl ,GroupNum = 700 ,SortOrder = 3 ,SqlCode = 'go' ,Section = 20 from dbo.ColumnsExt a ) select * from x order by LevelSort, TableName, GroupNum, SortOrder
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.