September 8, 2020 at 2:26 pm
Hi,
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
UNION ALL
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.
Thanks
September 8, 2020 at 3:46 pm
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?
September 8, 2020 at 6:42 pm
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.
Thanks
September 8, 2020 at 8:48 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 8, 2020 at 10:01 pm
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply