Solved

Deleting records into another table for analysis

Posted on 2013-06-08
4
368 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 300 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 200 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 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