Solved

Deleting records into another table for analysis

Posted on 2013-06-08
4
366 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 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 26

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 26

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sqlquerystress - To test db performance 8 42
SQL Help 27 45
SSRS 2013 - Overlapping reports 2 22
sql server concatenate fields 10 35
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

773 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