6

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?

Alex
  • 2,681
  • 3
  • 28
  • 43
  • 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] – tpither Jul 23 '14 at 12:55
  • 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. – Andrew Jul 23 '14 at 14:23

1 Answers1

16

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.

Vlad Z.
  • 3,401
  • 3
  • 32
  • 60
datacentricity
  • 1,099
  • 9
  • 15
  • Just tested this and it works, thanks. I used AssertResultSetsHaveSameMetaData to check that the table structure is the same – Alex Oct 03 '14 at 10:03
  • @Alec, I agree `AssertResultSetsHaveSameMetaData` is a great way of testing the whole table contract in one test – datacentricity Oct 04 '14 at 08:28
  • 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? – dim_user Jul 12 '18 at 16:53
  • @Saul - yes that is correct. tSQLt will ignore columns in the actual table that are not present in the expected table. – datacentricity Jul 13 '18 at 13:39