Data mapping & migration tool

  • Hi Guys,

    I am developing one automation tool for data migration from one table to other table, here i am looking for one function or SP for which i will pass source column and destination column as input parameter and want output parameter to return true when source column data is compatible to copy to destination column if not then it should return false.

    For example if source column is varchar and destination column is integer, the script should check all the data in source column in good enough to move to integer column or not and return the output flag. I want a script to work this for all types of data types. Any suggestions it will be helpful.

  • shivanath,

    Today is your lucky day. 🙂 I wrote this several years ago to do the exact task you're looking for.

    Using the datatype conversion grid found here : http://msdn.microsoft.com/en-us/library/ms187928.aspx

    1. We first give each dataType a value that correlates to the binary column shown in the chart:

    I.E.

    binary= 1

    varbinary= 2

    char= 4

    varchar= 8

    nchar = 16

    nvarchar = 32

    ...

    ...

    hierarchyid = 2147483648

    2. If the datatype can convert (either implicit or explicit) turn that bit on.

    3. Add that datatype's row's values together to get it's conversion value.

    4. We'll bitwise AND this value and the target datatype's ID = anything over 0 is a successful conversion.

    Calculation of the mapping value can be seen here. This example shows the explicit chart.

    First, the table

    ---- create the conersion table

    CREATE TABLE [dbo].[dataTypeConversionMap](

    [nDex] BIGINT NOT NULL PRIMARY KEY CLUSTERED,

    [dataTypeName] [varchar](20) NOT NULL,

    [implicitBit] [bigint] NOT NULL,

    [explicitBit] [bigint] NOT NULL

    ) ON [PRIMARY]

    GO

    Second, populate the binary values

    ---- insert the decimal value of the binary values

    INSERT [dbo].[dataTypeConversionMap] (nDex,dataTypeName,implicitBit,explicitBit)

    VALUES

    (1,'binary',4093587471,0)

    ,(2,'varbinary',4093587471,0)

    ,(4,'char',4286578684,8388611)

    ,(8,'cvarchar',4286578684,8388611)

    ,(16,'nchar',4253024252,8388611)

    ,(32,'nvarchar',4253024252,8388611)

    ,(64,'datetime',268439548,16773123)

    ,(128,'smalldatetime',268439548,16773123)

    ,(256,'date',268438780,3)

    ,(512,'time',268438780,3)

    ,(1024,'datetimeoffset',268438524,3)

    ,(2048,'datetime2',268437500,3)

    ,(4096,'decimal',285208831,12291)

    ,(8192,'numeric',285208831,12291)

    ,(16384,'float',276820223,0)

    ,(32768,'real',276820223,0)

    ,(65536,'bigint',285208831,0)

    ,(131072,'int',285208831,0)

    ,(262144,'smallint',285208831,0)

    ,(524288,'tinyint',285208831,0)

    ,(1048576,'money',285208831,0)

    ,(2097152,'smallmoney',285208831,0)

    ,(4194304,'bit',285208831,0)

    ,(8388608,'timestamp',50278607,0)

    ,(16777216,'uniqueidentifier',268435519,0)

    ,(33554432,'image',8388611,0)

    ,(67108864,'ntext',671088700,0)

    ,(134217728,'text',603979836,0)

    ,(268435456,'sql_variant',0,25165823)

    ,(536870912,'xml',1610612736,63)

    ,(1073741824,'clr udt',536870912,63)

    ,(2147483648,'hierarchyid',0,63)

    GO

    Now the function

    ---- create the function

    CREATE FUNCTION [dbo].[fn_willConvert]

    (@fromType VARCHAR(20)

    ,@toType VARCHAR(20)

    ,@conversionType CHAR(1) -- 'I'mplicit, 'E'xplicit, 'A'ny

    )

    RETURNS BIT

    AS

    BEGIN

    /*

    INPUT: Pass in a source and target datatype, and the conversion type

    OUTPUT: 1 or 0 (True or False) whether this conversion is legal

    WRITTEN BY: Jason L. Selburg

    CREATED: 02/26/2010

    Using the datatype conversion grid found here :http://msdn.microsoft.com/en-us/library/ms187928.aspx

    1. Give each dataType a value that correlates to the binary column shown in the chart:

    I.E.

    binary= 1

    varbinary= 2

    char= 4

    varchar= 8

    etc ...

    2. If the datatype can convert (either implicit or explicit) turn that bit on.

    3. convert the sum of that datatype's row gives you it's conversion value

    4. Bitwise AND this value and the target datatype's ID = anything over 0 is a successful conv.

    Refer to the dbo.dataTypeConversionMap table for values

    */

    DECLARE

    @willConvert BIT

    ,@toID BIGINT

    -- get the datatype ID

    SELECT @toID = nDex FROM dbo.dataTypeConversionMap WHERE dataTypeName = @toType

    SELECT

    @willConvert =

    CASE

    WHEN

    CASE @conversionType

    WHEN 'I'

    THEN implicitBit & @toID

    WHEN 'E'

    THEN explicitBit & @toID

    ELSE

    implicitBit & @toID + explicitBit & @toID

    END > 0

    THEN 1

    ELSE 0

    END

    FROM dbo.dataTypeConversionMap

    WHERE dataTypeName = @fromType

    RETURN @willConvert

    END

    GO

    Now you're ready to execute and test.

    Example 1: Is the conversion of a datetime datatype to char Explicit? Implicit? or can it be converted at all

    SELECT [dbo].[fn_willConvert]('datetime', 'char', 'E')

    SELECT [dbo].[fn_willConvert]('datetime', 'char', 'I')

    SELECT [dbo].[fn_willConvert]('datetime', 'char', 'A')

    Example 2: Is the conversion of a image datatype to timestamp Explicit? Implicit? or can it be converted at all

    SELECT [dbo].[fn_willConvert]('image', 'timestamp', 'E')

    SELECT [dbo].[fn_willConvert]('image', 'timestamp', 'I')

    SELECT [dbo].[fn_willConvert]('image', 'timestamp', 'A')

    Example 3: Is the conversion of a datetime datatype to decimal Explicit? Implicit? or can it be converted at all

    SELECT [dbo].[fn_willConvert]('datetime', 'decimal', 'E')

    SELECT [dbo].[fn_willConvert]('datetime', 'decimal', 'I')

    SELECT [dbo].[fn_willConvert]('datetime', 'decimal', 'A')

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Let me know if this works for you. 🙂

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • HI Jason,

    You saved my day 🙂 Thanks a lot 🙂 but here one more thing i need to add is, i want to check the data exists in the column. Suppose i have a varchar field but all the data in that column is integer value then this column data can be moved to any integer column but if even one row in that column is not numeric then it should fail and should return the rows details which will fail the conversion. Is there any workaround for this ?

  • That's going to be a bit more difficult since you're not comparing the column's datatype, but the actual data's datatype.

    I'm not sure how you could go about that without dynamic SQL or possibly in SSIS, sorry .... 🙁

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply