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