Solved

Deleting records into another table for analysis

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

932 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now