How do I ignore a column in a tSQLt AssertEqualsTable?

Question

Is it possible to ignore certain columns that are almost definitely going to be different in a tSQLt AssertEqualsTable? Examples would be primary keys from the two results tables, insert/update date stamps, and so on.

I have been working around this by selecting only the relevant columns into new temp tables and comparing those instead, but this means extra work and extra places to make mistakes. Not a lot, sure, but it adds up over dozens or hundreds of tests.

A built-in or simple way to say 'compare these two tables but ignore columns X and Y' would be very useful. Is there a better solution than the one I'm using?

Youngran

I've been wondering the same thing. My solution was to dump the results of the stored procedure under test into a temporary table, then drop the columns I'm not interested in testing: EXEC usp_UpdateUserTable Param1, Param2 SELECT * INTO #actual FROM Users `ALTER TABLE #actual DROP COLUMN [LastUpdated] – Nard

If you give an example of a test where you are experiencing the issue it may be helpful. I can't tell from the question how experienced you are at TDD and unit testing or how well you know tSQLt. – Sop

Answer 1

All you need to do is populate an #expected table with the columns you are interested in. When AssertEqualsTable does the comparison it will ignore any columns in the #actual table that don't exist in the #expected table.

Arnelle

Just tested this and it works, thanks. I used AssertResultSetsHaveSameMetaData to check that the table structure is the same – Youngran

@Alec, I agree AssertResultSetsHaveSameMetaData is a great way of testing the whole table contract in one test – Arnelle

Thanks for this solution, just to confirm, all we need to do is create #expected with the columns we are interested in right? and then populate it? – Rejoin

@Saul - yes that is correct. tSQLt will ignore columns in the actual table that are not present in the expected table. – Arnelle

Annoyingly, I believe that this contradicts the documentation 'AssertEqualsTable compares the contents of two tables for equality. It does this by comparing each row of the tables for an exact match on all columns. If the tables do not contain the same data, the failure message displays which rows could not be matched.' tsqlt.org/user-guide/assertions/assertequalstable – Fleda