mbt1
commented
1 year ago It is a good practice to move the data you are interested in into a temporary table (like #actual) and then create an #expected table from it using the SELECT * INTO syntax. That way you can insert into the expected table without having to mention column names and also compare only the columns you are interested in - again without mentioning them. This, while introducing two more statements, removes duplication from the test code and with that makes future maintenance easier.
I'm going to close this as "not necessary". I hope that you can live with the solution above.
There are many use cases where you want to test Actual/Expected table equality that is currently impossible when auto-generated values are involved. The most common one is a GETDATE() value, which are VERY frequently used as CreatedDate, ModifiedDate, etc. GUID values are another obvious example.
Please add a delimited input parameter to AssertEqualsTable so we can pass in the field(s) we want the AssertEqualsTable process to ignore in the comparison. In my current situation I am having to refactor the triggers I am testing to remove the GETDATE() and replace it with a hard-coded value just so the tests will be able to function. This obviuosly violates several fundamental tennants of unit testing. I know that this may well be a significant undertaking, but I think it will be incredibly useful.
In the mean time, if anyone has any more appropriate workarounds I would love to hear them!