Sql-server – copy/sync only data between 2 database with non identical table structure

sql-server-2012

I have the following issue and was wondering if someone knows if this is possible.

We have a MS SQL Server 2012 Instance with 2 databases database A and database B.

On Database A the data from 6 tables ( not the tables itself) has to be copied or synced to tables with the same name on Database B . I cannot use the sync within sql server because the layout of the tables is slightly different.

I got it to work in 3 steps now :

  1. remove constraints from tables in database B and delete entire content from tables
  2. Copy data with SSIS package from A to B
  3. Re applying constrains from tables in database B

This is not a desirable situation users that have a connection to database B lose connection ( duh!! )

My question : Is it possible to copy or sync only the ( modified ) table data from A to B without users having to leave the database for small period of time.
I hope my explanation is a bit clear…

Best Answer

Alright so this is an intro on how to accomplish this using SSIS. This is just a base model to get you going you're going to need to tune this based on your specific database to improve performance. This tutorial also assumes you know how to set connection managers. Keep that in mind.

First off you're going to want to create and then open a dataflow task. Then you want to add two OLE DB Sources, one Merge Join operation, one conditional split, one (possibly two) OleDB destinations, and one (possibly zero) OLEDB commands.

InitialStart

Once you've done this we will need to build the query for this table my example is just a calendar table.

SELECT 
    [BaseDate],
    [YearNumber],
    [MonthNumber],
    [DayNumber],
    [DayofWeek],
    [DayofYear],
    [WeekNumber],
    [Holiday],
    [WorkDay],
    [JulianDate],
    [BusinessDay],
    [Quarter]
FROM [Calendar]
ORDER BY [BaseDate] -- Very important to order by your primary key columns

Open your OLEDB Source.

Source

NOTE:By use Identical column names I mean in each source.

Once you've done this right click on the source and go to the advanced editor.

IsSorted

SortKey

Rinse and repeat for your second OLEDB Source. Once this is connect the source table to the Merge. This will prompt you for either Left or Right Input. The source table should always be the left side.

Once both are connected Open the Merge Join operator.

Merge

Open the conditional split and add this.

Connect the Merge Join Operator to the Conditional Split

Split1

Then connect that to your OLE DB Destination map the columns and boom you have your insert. Now for the tricky part. You will have to write an expression for comparing all of those columns. However if you're lazy like me you will use this stored procedure I made to do it for you. Simply create this stored procedure on the database you are pulling from and then execute with that table name in the parameter.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[MakeSSISColumnCompare] 
        (@TableName VARCHAR(50))
AS


DECLARE 
        @SqlCmd VARCHAR(max)
        ,@ColumnName VARCHAR(50)
        ,@DataType VARCHAR(50)
        ,@Nullable VARCHAR(3)
        ,@Count INT

DECLARE ColCsr CURSOR FOR 
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName

OPEN ColCsr
SET @Count = 0

FETCH NEXT FROM ColCsr INTO @ColumnName, @DataType, @Nullable
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @ColumnName NOT IN ('CreatedDateTime','UpdateDateTime')
    BEGIN
        IF @Count = 0
                SET @SqlCmd = 
                CASE 
                        WHEN @Nullable = 'NO' THEN '[' + @ColumnName + ']!=[' + @ColumnName + ' (1)]'
                        WHEN @DataType LIKE '%CHAR%' THEN '(ISNULL([' + @ColumnName + '])?"XXXX":[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?"XXXX":[' + @ColumnName + ' (1)])'
                        WHEN @DataType LIKE '%INT%' THEN '(ISNULL([' + @ColumnName + '])?-99:[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?-99:[' + @ColumnName + ' (1)])'
                        WHEN @DataType = 'DATETIME' THEN '(ISNULL([' + @ColumnName + '])?"(DT_DBDATE)1/1/1900":[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?(DT_DBDATE)"1/1/1900":[' + @ColumnName + ' (1)])'
                        WHEN @DataType IN ('MONEY', 'NUMERIC', 'DECIMAL','FLOAT') THEN '(ISNULL([' + @ColumnName + '])?0:[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?0:[' + @ColumnName + ' (1)])'
                        WHEN @DataType = 'BIT' THEN '[' + @ColumnName + ']!=[' + @ColumnName + ' (1)]'
                        ELSE 'TRUE' END
        ELSE 
                SET @SqlCmd = @SqlCmd + 
                CASE 
                        WHEN @Nullable = 'NO' THEN '||[' + @ColumnName + ']!=[' + @ColumnName + ' (1)]'
                        WHEN @DataType LIKE '%CHAR%' THEN '||(ISNULL([' + @ColumnName + '])?"XXXX":[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?"XXXX":[' + @ColumnName + ' (1)])'
                        WHEN @DataType LIKE '%INT%' THEN '||(ISNULL([' + @ColumnName + '])?-99:[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?-99:[' + @ColumnName + ' (1)])'
                        WHEN @DataType = 'DATETIME' THEN '||(ISNULL([' + @ColumnName + '])?(DT_DBDATE)"1/1/1900":[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?(DT_DBDATE)"1/1/1900":[' + @ColumnName + ' (1)])'
                        WHEN @DataType IN ('MONEY', 'NUMERIC', 'DECIMAL','FLOAT') THEN '||(ISNULL([' + @ColumnName + '])?0:[' + @ColumnName + '])!=(ISNULL([' + @ColumnName + ' (1)])?0:[' + @ColumnName + ' (1)])'
                        WHEN @DataType = 'BIT' THEN '||[' + @ColumnName + ']!=[' + @ColumnName + ' (1)]'
                        ELSE '||TRUE' END
        SET @Count = @Count + 1
    END
    FETCH NEXT FROM ColCsr INTO @ColumnName, @DataType, @Nullable
END
SELECT @SqlCmd AS SqlSelect

CLOSE ColCsr
DEALLOCATE ColCsr

GO

Then execute like so.

EXEC    [dbo].[MakeSSISColumnCompare]
        @TableName = N'YourTableName'

This should give you the String you need to compare. You will want to delete the piece of this string that compares the key values. In my case turning this.

[BaseDate]!=[BaseDate (1)]||[YearNumber]!=[YearNumber (1)]||[MonthNumber]!=[MonthNumber (1)]||[DayNumber]!=[DayNumber (1)]||[DayofWeek]!=[DayofWeek (1)]||[DayofYear]!=[DayofYear (1)]||[WeekNumber]!=[WeekNumber (1)]||[Holiday]!=[Holiday (1)]||[WorkDay]!=[WorkDay (1)]||[JulianDate]!=[JulianDate (1)]||[BusinessDay]!=[BusinessDay (1)]||[Quarter]!=[Quarter (1)]

Into this.

[YearNumber]!=[YearNumber (1)]||[MonthNumber]!=[MonthNumber (1)]||[DayNumber]!=[DayNumber (1)]||[DayofWeek]!=[DayofWeek (1)]||[DayofYear]!=[DayofYear (1)]||[WeekNumber]!=[WeekNumber (1)]||[Holiday]!=[Holiday (1)]||[WorkDay]!=[WorkDay (1)]||[JulianDate]!=[JulianDate (1)]||[BusinessDay]!=[BusinessDay (1)]||[Quarter]!=[Quarter (1)]

This is why keeping the column names the same was important earlier. This allows that stored procedure to build the comparison string for us. Now simply copy and paste that into the second place of the conditional split.

Update

Now either that to an OLEDB command to update the necessary table or send to to a staging table and then perform and traditional SQL update from there. This will depend on the size of the dataset being updated as to which performs better.

I would recommend including a timestamp system of some sort as well. This will allow you to limit the amount of rows you grab in your initial select.

Sorry this is isn't using 2012 data tools but I don't have access to that quite yet.

Related Question