Data validate tools (ETL tools) for SQL server

Question

I have some data in Excel and need to import into database. Is there any tool that can validate and maybe clean the data? Does Red Gate have such tool?

The input will be Excel. Given table constraints, eg. CHECK, UNIQUE KEY, datetime format, NOT NULL. Desire output should be as least shows which lines are having problems, and then fix some trivial error automatically, like fill in default value for NULL columns, automatically correct datetime format.

I know using Python can build such a script. But just wonder what's the popular way to do this. Thanks.

Answer 1

You'd normally load a staging table and perform validation on that. The staging table will typically have more varchar columns that the "real" table, all nullable, no constraints etc

Example to find rows with duplicates

SELECT COUNT(*), UniqueKey FROM StagingTable GROUP BY UniqueKey HAVING COUNT(*) > 1

You then run the assorted not null, check code etc one by one

Then when you're satisfied, you'd load the real table from your staging table.