Data Migration without SSIS

Table of Contents [Hide/Show]

Overview¶

Purpose¶

When copying data between different databases, you have to consider foreign key constraints in the target database, or you'll violate one of them and the process will fail. The following article outlines how to copy data from one database to another, using SQL scripts. Doing this via SSIS (SQL Server Integration Services) was considered, but determined to be much more complex to develop.

Assumptions¶

At least on a conceptual level, the two databases have the same (or close to the same) layout. This makes this solution ideal for copying data between different instances (e.g., environments) of the same database.
Each target table must be mapped to exactly one source table.
Each target column must be mapped to exactly one source column.
All columns in the same target table must be mapped to columns in the same source table. "Cross-table" mappings are not allowed.
Columns can exist in the source that don't exist in the target. These columns are simply ignored.
Columns that exist in the target that don't exist in the source must be either nullable or have a default value.

The Process¶

The data copy proceeds in the following steps

Create the Migration database and database objects. This database will be used to store the mappings between the two databases, and to generate the migration script.
If necessary, create linked servers objects available to the Migration database.
Create SYNONYM objects that point to the source and target database's system views.
Initialize the tables in the Migration database.
Assign a source table to each target table of interest, and assign a source column to each target column of interest.
Generate and execute the data migration script.

The Migration Script¶

The migration script generated will delete all the data in the every target table, then populate each target table with the data mapped from its source table. The DELETE and INSERT statements are put in the correct order so as to not violate any foreign key constraints, and a SET IDENTITY_INSERT ON statement is executed before each INSERT statement (where appropriate), so that the target database ends up being as close a copy to the source as possible.

Walk-Through¶

Step 1 — Create Linked Servers¶

This step is required in order to setup your SYNONYM objects in a later step. How to create a linked server is beyond the scope of this article.

Step 2 — Create the MIGRATION Database¶

This is done via SQL Server Management Studio by navigating to Object Explorer > (server of interest) > Databases folder > (right-click) > New Database; or via a CREATE DATABASE statement. If you're wanting to copy data between two databases for which you may not have the necessary security rights to, your best option may be to create a database on your local PC and use linked servers to the two databases of interest.

Step 3 — Create the MIGRATION Database Objects¶

Being sure to change the names of MySourceInstance, MySourceDb, MyTargetInstance, and MyTargetDb, create the MIGRATION database objects using the script below.

{copytext|CreateMigrationDatabase}

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

Step 4 — Initialize the MIGRATION Database¶

Being sure to change the values of MySourceDb and MyTargetDb, initialize the tables in the MIGRATION database via the following script.

{copytext|FillMigrationDatabase}

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

Step 5 — Map the Tables and Columns¶

Using the Access 2007 database attached to this article in a ZIP file, manually assign the source table for each target table, and the source column for each target column.

If the source and target databases have the same layout, you can use the following script to make these assignments for you automatically. Tables and columns will be matched on name. Before running the script, be sure to change MySourceId and MyTargetId, which can be determined by examining the contents of the dbo.DbMaster table.

{copytext|MapTablesAndColumns}

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

Step 6 — Generate the Migration Script¶

Execute this script with the results being sent to a grid (Query menu > Results to > Results To Grid).
Click the SqlCode heading to select the contents of the entire column, and copy the contents to the clipboard
Paste the script to a new query window.

{copytext|GenerateMigrationScript}

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