How Can I verify the Data In Bulk Insert

Say i am inserting the data into the database using bulk insert that is开发者_开发问答 from asp.net to sol 2008.Now i Want to Validate the data How can i do that.Pls Help

Well for the most part your staging table's design should be such that it won't allow nulls where they should not be and the data types of the fields should be the types you expect. So say a field is a datetime then you won't be able to insert any data expect data that is a datetime type. Similarly if a field is defined to be an int then the data can't be a string type etc.

Try and use the feature/capabilities of the database to the extent possible by defining the correct datatypes for your fields. If any additional validations need to be done then you can run some stored procedure that does the other validations after import has finished.

Further, triggers on your destination table could help with ensuring valid data too.

For "data already exists" depending on what you want to do, you could use the MERGE statement to insert the data into the destination. The MERGE statement will allow you to ignore duplicates or update them (maybe some field's values have changed and you need to new values to be updated).

I always bulk insert into a staging table (a table representive of the final home, but not used by the transactional system).

This:

• allows me to validate through TSQL (including checking any related data in other tables etc)
• ensures the final INSERT (into the real table) is fully logged (bulk inserts aren't, necessarily)
• ensures nobody reads the incomplete/unvalidated data
• prevents any blocks etc (to or from the live system)
• insulates from problems if the insert fails half way through

In your case, the first bullet is most paramount, but all the others matter too.

Also, since the data is coming from .NET, make sure you are using SqlBulkCopy if this truly is a bulk insert.