Comparing multiple tables with one another

Question

I do not necessarily spend my time creating sql-queries, I maintain and search for mistakes in databases. I constantly have to compare two types of tables, and if the database is small, I dont mind just writing a small query. But at times, some databases are huge und the amount of tables is overwhelming.

I have one table-type that has compressed data and another that has aggregates comprised of the compressed data. At times, the AggregateTables are missing some IDs, some data was not calculated. If it is just one AggregateTable, I just compare it to its corresponding compressed table and i can immediately see what needs to be recalculated(code for that is shown below).

 select distinct taguid from TLG.TagValueCompressed_0_100000
 where  exists
 (select * from tlg.AggregateValue_0_100000 where 
 AggregateValue_0_100000.TagUID = TagValueCompressed_0_100000.TagUID) 

I would like to have a table, that compares all tables with another and spits out a table with all non existing tags. My SQl knowledge is at its infancy, and my job does not require me to be a sql freak. But a query that does said problem, would help me alot. Does anyone have any suggestions for a solution?

Relevant comlumns: Taguids, thats it.

Optimal Table:

 Existing Tags            missing Tags
1
2
3
4
.
.

Numbers meaning what table : "_0_100000", "_0_100001" ...

Answer 1

So let's assume this query produced the output you want, i.e. the list of all possible tags in a given table set (0_100000, etc.) and columns denoting whether the given tag exists in AggregateValue and TagValueCompressed:

SELECT '0_100000' AS TableSet
     , ISNULL(AggValue.TagUID, TagValue.TagUID) AS TagUID
     , IIF(TagValue.TagUID IS NOT NULL, 1, 0) AS ExistsInTag
     , IIF(AggValue.TagUID IS NOT NULL, 1, 0) AS ExistsInAgg
  FROM (SELECT DISTINCT TagUID FROM tlg.AggregateValue_0_100000) AggValue
  FULL OUTER
  JOIN (SELECT DISTINCT TagUID FROM TLG.TagValueCompressed_0_100000) TagValue
    ON AggValue.TagUID = TagValue.TagUID

So in order to execute it for multiple tables, we can make this query a template:

DECLARE @QueryTemplate NVARCHAR(MAX) = '
SELECT ''$SUFFIX$'' AS TableSet
     , ISNULL(AggValue.TagUID, TagValue.TagUID) AS TagUID
     , IIF(TagValue.TagUID IS NOT NULL, 1, 0) AS ExistsInTag
     , IIF(AggValue.TagUID IS NOT NULL, 1, 0) AS ExistsInAgg
  FROM (SELECT DISTINCT TagUID FROM tlg.AggregateValue_$SUFFIX$) AggValue
  FULL OUTER
  JOIN (SELECT DISTINCT TagUID FROM TLG.TagValueCompressed_$SUFFIX$) TagValue
    ON AggValue.TagUID = TagValue.TagUID';

Here $SUFFIX$ denotes the 0_100000 etc. We can now execute this query dynamically for all tables matching a certain pattern, like say you have 500 of these tables.

DECLARE @query NVARCHAR(MAX) = ''
-- Produce a query for a given suffix out of the template
-- suffix is the last 8 characters of the table's name
-- combine all the queries, for all tables, using UNION ALL
SELECT @query += CONCAT(REPLACE(@QueryTemplate, '$SUFFIX$', RIGHT(name, 8)), ' UNION ALL ')
  FROM sys.tables
 WHERE name LIKE 'TagValueCompressed%';

-- Get rid of the trailing UNION ALL
SET @Query = LEFT(@Query, LEN(@Query) - LEN('UNION ALL '));

EXECUTE sp_executesql @Query

This will yield combined results for all matching tables.

Here is a working example on dbfiddle.