?
Solved

Deleting records into another table for analysis

Posted on 2013-06-08
4
Medium Priority
?
372 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 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

770 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