Validate data while migration from one server to another server in SQL server.

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

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

Posted 26-Jun-18 0:01am

suneel kumar gupta

Updated 25-Jul-18 20:01pm

v2

Add a Solution

Comments

Kornfeld Eliyahu Peter 26-Jun-18 6:27am

If a column is type X and NULL/NOT NULL, you will not be able to insert any other data, so why check?! SQL will throw an exception for you...

suneel kumar gupta 26-Jun-18 7:16am

I have column which allow bit value or null value then how should i validate this condition.

Kornfeld Eliyahu Peter 26-Jun-18 7:25am

You should not - SQL will, and if the value does not fit you will have an exception!!!

suneel kumar gupta 26-Jun-18 7:33am

actually I want to migrate data from another server through scheduler (SQL JOB) and if data validation failed then I want to store in which columns data are invalid.

Lets say I have 5 column and while validate data in 2 column data are invalid than store column name and custom error defined for than column.

Kornfeld Eliyahu Peter 26-Jun-18 7:36am

If you are moving from SQL to SQL, than how data can be invalid? Are you moving data from NVARCHAR to INT, or FLOAT to BIT? IF columns in source and target has the same type declaration, than you have fear nothing...

suneel kumar gupta 26-Jun-18 7:38am

yes , may be because the data providing from different different vendor.

Kornfeld Eliyahu Peter 26-Jun-18 7:43am

Vendor does not matter. AS long as you are moving BIT to BIT and INT to INT...
If you are to CONVERT data you can not have something automatic, but you have to write queries for table-to-table convert...

Mike V Baker 26-Jun-18 10:39am

I think the thought there is that the other vendor's database might not be set up with those constraints so suneel is trying to verify the data first.

Mike V Baker 26-Jun-18 10:43am

In this case (the column6 bit value) I would probably treat incoming 0 as 0, NULL as NULL, and anything else as 1
I have a question for you, this scheme to validate the incoming data... how do you record that some records didn't pass the validation? Is there an exception report with a TEXT field to contain the incoming raw data?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 11621026 · Answer 1 · 2018-07-25T20:02:00

Solution 2

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

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

Posted 25-Jul-18 20:02pm

Member 11621026

Member 11621026 · Answer 2 · 2018-07-24T19:10:00

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