Database Records Match to Baseline Check point and report

[AntoniusMomac]

Apologizes in advance for the long title.

I need the help of the talented people here in the MS world. If anyone has any suggestion for the following I would appreciate it very much. I do not have enough intelligence to come up with a solution for the following need:

Testing need:

I need to compare a specific record set for a given Scenario ifrom a SQL2005 database; I would like to also have a way of capturing and easily updating my baseline checkpoint, i.e. the record set that I will be matching against. Also, I would really like to report on which fields fail the comparison.

Ex of RS:

Col_1 | Col_2 | Col_3
A | 1234 | 10/23
A | 3426 | 10/24
B | 1245 | 12/1
C | 89 | 12/1
C | 784 | 11/8


Col_1 Represents the scenario; the records are written when using my System. After I finish each senario, I need to verify that all the feilds have been written correctly.

My though was to have a loop for the Scenario A,B and C in this case that queries the database and writes the record set to a text file. Then I could compare the text file to compare the results to my baseline text file. Also, the create the baseline I figure that I could comment on and off code in the loop to copy over the results file to create and update the baseline file.

Does this sound like a do-able solutions, or can anyone suggest something better.

Also, my final issue is how would I go about doing the compares efficently, and would it be possible to report on the result set?

I really have no idea, so all ideas are welcome. Many, MANY thanks in advance.

[Marcus Tettmar]

Antonius,

I have just replied to your support ticket on the same subject with some ideas. I hope I've understood your requirements properly. I see no harm in using your idea of saving a snapshot to a text file and then comparing text files. But another way would be to store the snapshot in another SQL table and then compare tables. A union join could be used to make a comparison - if the join returns records the tables are different.

http://weblogs.sqlteam.com/jeffs/archiv ... /2737.aspx