Data Validation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Data Validation

Author

Topic

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2007-09-22 : 17:20:10

Hi guys I am fairly new to t-sql. I am sure there are SPs or scripts that I can use to create a procedure that will do the data validation in the staging table...

Client send us data and often some of the records have bad values... what i have to create is a process that will check for those values and update a flag in the staging table for each column if the data is not valid....

Please help me out if you have something that can be used for this.

Thanks a lot help in advance.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-22 : 19:08:27

well you have to have a template of what is correct data.
validate every column (compare it to the template)
and if it's not then update it's flag.

update yourTable
set isRowOK = false
where columnValidationFails

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Kristen
Test

22859 Posts

Posted - 2007-09-23 : 02:22:26

We add two columns to Staging tables:

ErrorNumber and ErrorMessage

The ErrorNumber contains the error of the most recent found. 0 means that NO error were found

In ErrorMessage we concatenate human-readable-messages for each error found. Users can use these to fix the data.

We use a series of UPDATE statements, as Spirit described, to make each validation.

So maybe something like:


UPDATE MyStagingTable
SET ErrorNumber = 1,
    ErrorMessage = COALESCE(ErrorMessage+', ', '') + 'Bad transaction date'
WHERE IsDate(MyTransactionDateColumn) <> 1

Kristen

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2007-09-23 : 12:23:26

Thanks a lot guys... I guess I can create triggers for each column or bunch of update statements for each column that will update the flags appending the error messages and that should work. So in that case, I should insert all the data in nvarchar columns and then use the rules rite? Thanks again guys.

Kristen
Test

22859 Posts

Posted - 2007-09-24 : 02:04:24

"I should insert all the data in nvarchar columns and then use the rules rite?"

Yup. I would recommend using a column of the correct datatype if you know that the data will be in that format - so use an INT / DATETIME if you can - but of course if you try to import "invalid" data into such datatype columns the import will fail ... and then you will have to revert to varchar/Nvarchar

Kristen

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2007-09-24 : 16:18:06

How can I used a table that has column names (for staging tables) and data-type and compare that to the staging table and do the validation?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources