?
Solved

Deleting records into another table for analysis

Posted on 2013-06-08
4
Medium Priority
?
377 Views
Last Modified: 2013-06-08
I have a project where by process of elimination, I need to find which records fall into a final tier. So, basically I know the total amount and three out of the four tiers but need to get the final tier by this method.

Anyhow, I thought I could do a DELETE FROM into a temp table and it wouldn't be an issue but I'm finding that's not the case.

I tried adding the OUTPUT DELETED.* and having it go into a runtime variable which gets me the records, which is great -- however, then I want to run summary stats on this and it is not working.

Posting here the last steps I did and hoping someone may offer advice on how I can delete records into something that I can then query and run stats (like COUNT, SUM) on.

Thank you in advance!

DELETE FROM #temp_CLAIMS_MC -- total claims
OUTPUT DELETED.* INTO @TIER3 -- final tier needed
WHERE [DOCUMENT] NOT IN(SELECT [DOCUMENT] FROM #TIER_CLAIMS) -- temp table of tier 1+2+4


--Display the results of the table variable.

SELECT [DOCUMENT]      
, CAPACITY_CD      
, FIRST_DOS      
, TOTAL_PD
FROM @TIER3
GO

Then want to do summary stats like:

SELECT COUNT(DISTINCT [DOCUMENT]) AS TOT_CLAIMS
, SUM(TOTAL_PD) AS TOT_PD
FROM @TIER3            

but get error "Must declare the table variable "@TIER3"."

And have already defined it earlier in the session as:

DECLARE @TIER3 TABLE (
[DOCUMENT]      NVARCHAR(9) NOT NULL
, CAPACITY_CD      NVARCHAR(4) NOT NULL
, FIRST_DOS      DATETIME
, TOTAL_PD INT NOT NULL);
0
Comment
Question by:britpopfan74
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 1200 total points
ID: 39232116
the issue you are facing is with the batch termination command GO
once SQL sees the GO statements then it ends the batch....
what it means to you?
   it removes all the local variables and the local temporary tables from the memory.

so, you @Tier3 table is removed after the go statement in your select clause, remove it from the select and it should start working fine...

the below one should work just fine...
SELECT [DOCUMENT]      
, CAPACITY_CD      
, FIRST_DOS      
, TOTAL_PD 
FROM @TIER3


SELECT COUNT(DISTINCT [DOCUMENT]) AS TOT_CLAIMS
, SUM(TOTAL_PD) AS TOT_PD
FROM @TIER3       

Open in new window

0
 
LVL 27

Assisted Solution

by:skullnobrains
skullnobrains earned 800 total points
ID: 39232135
i'm not sure i understand but if you just want to select, why do you need to delete stuff in tmp tables ?

select * from all_records
left outer join #TIER_CLAIMS
on #TIER_CLAIMS.id = all_records.id
where #TIER_CLAIMS.id is null

or even

select * from all_records
left outer join (select * from tier1 UNION select * from tier2 UNION select * from tier3) as tiers
on tiers.id = all_records.id
where tiers.id is null

use select ... into if you want to store the results in another table
0
 

Author Closing Comment

by:britpopfan74
ID: 39232196
Thank you so much for both solutions -- gives me point of view to look at differently I hadn't realized.
0
 
LVL 27

Expert Comment

by:skullnobrains
ID: 39232279
you may also want to try

select * from all_records
where tiers.id not in (select id from tier1 UNION select id from tier2 UNION select id from tier3)

dont know which should be faster
this one should at least not create a temporary table with uneeded columns but the tmp table cannot be used as the result set.

if you are concerned about speed also change the other two to select only the columns you need before you compare the speed

actually if the query analyser is perfect, it should be exactly the same

happy coding
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Here is how to restore SQL Server database to the point in time.  Follow the step by step approach to restore your database at a specific point in time and also understand its alternate approach.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question