在SQL Server中从一台服务器迁移到另一台服务器时验证数据。 [英] Validate data while migration from one server to another server in SQL server.

问题描述

我想根据定义的数据类型和空值验证以下列的数据。



Column1数字（18,8）null



Column2 Varchar（10）not null



Column3 Int not null



Column4 int null



column5 bit not null



column6 bit null



column7 datetime null







- 如何查看价值column6是类型位



我尝试过：



我有尝试 -



选择

情况下，当Column1不为空且非对称（第1列）<> 1那么0结束1结束'Column1'，

情况，当Column2为空或者Column2 =''或LEN（Column2）> 50那么0 ELSE 1结束为'Column2'，

CASE WHEN ISNULL（Column3，''）=''或者Column3 LIKE'％[^ 0-9]％'然后0 ELSE 1结束为'Column3'，

CASE WHEN ISNU LL（Column4，''）<>''AND Column4 NOT LIKE'％[^ 0-9]％'然后0 ELSE 1 END作为'Column4'

情况当column7 IS NOT NULL AND ISDATE（column7）= 0那么50 ELSE 0 END作为'column7'



来自TABLE1

I want to verify below column's data according defined datatype and null values.

Column1 numeric(18,8) null

Column2 Varchar(10) not null

Column3 Int not null

Column4 int null

column5 bit not null

column6 bit null

column7 datetime null



-- How to check value in column6 is type bit

What I have tried:

I have tried--

SELECT
CASE WHEN Column1 IS NOT NULL AND ISNUMERIC(Column1)<>1 THEN 0 ELSE 1 END AS 'Column1',
CASE WHEN Column2 IS NULL OR Column2='' OR LEN(Column2)>50 THEN 0 ELSE 1 END AS 'Column2',
CASE WHEN ISNULL(Column3,'')='' OR Column3 LIKE '%[^0-9]%' THEN 0 ELSE 1 END AS 'Column3',
CASE WHEN ISNULL(Column4,'')<>'' AND Column4 NOT LIKE '%[^0-9]%' THEN 0 ELSE 1 END AS 'Column4'
CASE WHEN column7 IS NOT NULL AND ISDATE(column7)=0 THEN 50 ELSE 0 END AS 'column7'

FROM TABLE1

推荐答案

select isshared,case when isshared = 0 then 'ABC' when isshared=1 then 'XYZ' end as aaa from kk

我的观点是当我理解你的问题时。



否则有两种方法可以检查位数值



true false

0 1

For my point of view when i understand the question for you.

else there are two way to check for bit filed value

true false
0 1

你错过最后一行的逗号

you miss comma for last row

SELECT
CASE WHEN Column1 IS NOT NULL AND ISNUMERIC(Column1)<>1 THEN 0 ELSE 1 END AS 'Column1', 
CASE WHEN Column2 IS NULL OR Column2='' OR LEN(Column2)>50 THEN 0 ELSE 1 END AS 'Column2', 
CASE WHEN ISNULL(Column3,'')='' OR Column3 LIKE '%[^0-9]%' THEN 0 ELSE 1 END AS 'Column3',
CASE WHEN ISNULL(Column4,'')<>'' AND Column4 NOT LIKE '%[^0-9]%' THEN 0 ELSE 1 END AS 'Column4', 
CASE WHEN column7 IS NOT NULL AND ISDATE(column7)=0 THEN 50 ELSE 0 END AS 'column7'

FROM TABLE1

这篇关于在SQL Server中从一台服务器迁移到另一台服务器时验证数据。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！