How to get RowCount and ResultSet from CTE



    I have this query

    CREATE TABLE #Summary(SourceQueryRowCount INT, TargetQueryRowCount INT, QueryDiffRowCount INT)
    CREATE TABLE #CompareResult(DataSource varchar(50), ID int, Rule1 varchar(50), Rule2 varchar(50))

    ;WITH SourceQuery AS(
    SELECT * FROM (VALUES( 1, 'a','b'),(2, 'c','d'),(3,'e','f'))rs(id,rule1,rule2)WHERE rs.rule1 != 'a'),
    TargetQuery AS (
    SELECT * FROM (VALUES( 1, 'a','b'),(2, 'c','d'),(3,'e','f'))rs(id,rule1,rule2)
    INSERT INTO #CompareResultSELECT DataSource = 'SourceToTarget', * FROM SourceQuery EXCEPT SELECT DataSource = 'SourceToTarget', * FROM TargetQuery
    SELECT DataSource = 'TargetToSource', * FROM TargetQuery EXCEPT SELECT DataSource = 'TargetToSource', * FROM SourceQuery

    and I want to get rows count in variable or #Summary table and compare result set into table #CompareResult.

    I did this with create separate temp table for SourceQuery and TargetQuery and then compare it but as source and target queries result set are huge and i want to perform every thing in one set. is this possible.


  • Hi inayatkhan,

    and thank you for providing the data and the results for your question,  much appreciated!


    Unfortunately, I'm struggling with the logic as nothing is inserted into the #Summary table? Can you please elaborate on this?


    That's my question. how i can insert into #SummaryTable. I want to insert row into summary table with SourceQueryRowCount, TargetQueryRowCount and DataDiffTotalCount and insert into summary table. As result are huge so i dont want to execute again. Is there any way i can do that with one query.





  • if I understand right, you want to do an insert into 2 different temp tables from a CTE in a single query?  If so, I don't think that is possible.  You would need to go through the data more than once to do that sort of operation on it inside a CTE.

    Now, there are some tricks you can do to make the COUNT faster though at the cost of some TempDB space - if you turn your CTE into temporary tables instead of a CTE, you will get statistics on the tables and as such a COUNT will be incredibly fast.

    The advantage of using a temporary table (or table variable) for it is you can index it to improve performance of multiple operations on the single object (such as 2 SELECTs so you can handle 2 different INSERTs).

    Alternately, a bad idea you could do would be since the counts are all INTs in the SUMMARY table and you have an INT in your other table, you could do a 2nd UNION and put the counts in with a data source of something that will not exist in there (such as SUMMARY) and then take the data out of the table where the datasource='SUMMARY'.  Now why is this bad?  Because if the datatypes change on either side (int->smallint or int->bigint) you could get some issues.  Or if a 4th column shows up in Summary that you need to capture, you hit a snag too.  Plus, future you will hate yourself for doing something like that.  And any DBA or developer that looks at your code will wonder what you were thinking.  And I'd never let that code hit production like that as it is very hacky.  BUT it is another option.

    My preferred option would be to remove the CTE and use temp table or table variable.  Might be able to add indexes on the temp table/table variable as well to get a performance boost at the cost of some TempDB space.

  • Mr. Brian Gale wrote:

    if I understand right, you want to do an insert into 2 different temp tables from a CTE in a single query?  If so, I don't think that is possible.

    Nor do I. You're going to have to do this in steps.

    But remember that you can do this:

    INSERT #Tmp1(col1, col2)
    SELECT col1, col2 from table
    Where ...

    DECLARE @RowCount1 INT = @@ROWCOUNT;

    So you can get the temp table row counts into variables after performing the temp table inserts, without needing to do any more scans.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See for details of how to post T-SQL code-related questions.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply