如何在进行数据映射或迁移之前验证数据 [英] how to validate data before we do data mapping or migration

问题描述

我有一个任务，我需要将数据从一个table1移动到table2，我有源列和目标列，但在移动数据之前，有可能找出源列中的任何数据是否不适合目标列因为目标字段类型差异或字段长度不那么？



在场景中我有源列，它是char（1），我试图映射那列数据到int列，这里源列中的大多数行都有整数值但是在某些行中有一些字母字符，所以我想找出所有那些将要失败的行....逻辑应该适用于所有数据类型。



提前致谢!!

I have task where i need to move data from one table1 to table2, i have source columns and destination columns but before i move data is it possible to find out is there any data in source column doesn't not fit destination column because of destination field type difference or field length is less like that ?

Like in a scenario i have source column which is char(1) and i am trying to map that column data to int column, here most of rows in source column have integer values but there are some alpha characters in some rows so i want to find out all those rows which are going to fail....the logic should work for all data types.

Thanks in advance!!

推荐答案

看看这里：错误恢复下一个SQL Server 2005 [ ^ ]



但你应该知道没有像这种功能的黄金法则。只有一个人可以写它：你！因此，根据您的需要编写验证规则。



这是 CHAR（1）到<的想法code> INT 转换：

Have a look here: On Error Resume Next SQL Server 2005[^]

But you should know that there is no something like "the golden rule" for this kind of functionality. There is only one person who can write it: YOU! So, write validation rules depending on your needs.

Here is an idea for CHAR(1) to INT conversion:

SELECT CONVERT(INT, AkaIntField) AS NewInt
FROM (
    SELECT AkaIntField
    FROM TableName
    WHERE ISNUMERIC(AkaIntField)=1
) AS T

这篇关于如何在进行数据映射或迁移之前验证数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！