drop table select into - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

drop table select into

Author

Topic

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-01-28 : 10:39:40

I'm confused as to how this sp is working? Can you drop a table and then select into it without recreating it? I thought when you dropped a table it was gone.

sp:
drop table tmp_claimeligiblebalance

SELECT tsd_Claim.clpid,
Sum(tsd_Claim.cloutstandingamt) as sumoutstanding
INTO tmp_ClaimEligibleBalance

FROM tsd_Claim

GROUP BY tsd_Claim.clpid
HAVING sum(tsd_Claim.cloutstandingamt) <>0 AND sum(tsd_Claim.cloutstandingamt) IS NOT NULL
And max(tsd_Claim.clfromdos) < (cast(getdate() as smalldatetime) - 120) And max(tsd_Claim.clins) Is Not Null

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 10:45:42

The INTO part creates the table for you.

DROP TABLE	tmp_claimeligiblebalance
GO

SELECT		clpid,
		Sum(cloutstandingamt) as sumoutstanding
INTO		tmp_ClaimEligibleBalance
FROM		tsd_Claim
GROUP BY	clpid
HAVING		sum(cloutstandingamt) <> 0
		And max(clfromdos) < DATEADD(DAY, -120, getdate())
		And max(clins) IS NOT NULL

If you are going to reuse the table, why not just TRUNCATING the table?

TRUNCATE TABLE	tmp_claimeligiblebalance

INSERT		tmp_claimeligiblebalance
		(
			clpid,
			sumoutstanding
		)
SELECT		clpid,
		Sum(cloutstandingamt)
INTO		tmp_ClaimEligibleBalance
FROM		tsd_Claim
GROUP BY	clpid
HAVING		sum(cloutstandingamt) <> 0
		And max(clfromdos) < DATEADD(DAY, -120, getdate())
		And max(clins) IS NOT NULL

E 12°55'05.25"
N 56°04'39.16"

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-01-28 : 10:52:09

That is what I have suggested, I just wanted to make sure I was corrected. I wasn't sure the into would create it. Thank you for your response.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources