Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm fairly new to SQL so this may be an easy mistake, but I haven't been able to find a solid solution anywhere else. Problem is whenever I try to use my temp table, it tells me it cannot be used because there is already an object with that name. I frequently try switching up the names, and sometimes it'll let me work with the table for a little while, but it never lasts for long. Am I dropping the table incorrectly? Also, I've had people suggest to just use a permanent table, but this database does not allow me to do that.

create table #RandomTableName(NameOfObject varchar(50), NameOfCounter varchar(50), SampledValue decimal)

select vPerformanceRule.ObjectName, vPerformanceRule.CounterName, Perf.vPerfRaw.SampleValue

into #RandomTableName

from vPerformanceRule, vPerformanceRuleInstance, Perf.vPerfRaw
where (ObjectName like 'Processor' AND CounterName like '% Processor Time')
OR(ObjectName like 'System' AND CounterName like 'Processor Queue Length')
OR(ObjectName like 'Memory' AND CounterName like 'Pages/Sec')
OR(ObjectName like 'Physical Disk' AND CounterName like 'Avg. Disk Queue Length')
OR(ObjectName like 'Physical Disk' AND CounterName like 'Avg. Disk sec/Read')
OR(ObjectName like 'Physical Disk' and CounterName like '% Disk Time')
OR(ObjectName like 'Logical Disk' and CounterName like '% Free Space' AND SampleValue > 70 AND SampleValue < 100)
order by ObjectName, SampleValue

drop table #RandomTableName
share|improve this question
    
Side note: The order by in your select is totally useless - tables do NOT have an intrinsic sort order unless you create one. By ordering you push additional processing into the select part that is useless later sa #RandomTableName is NOT SORTED. –  TomTom May 31 '12 at 18:17
add comment

1 Answer

up vote 0 down vote accepted

You are creating the table first, which is fine, but then you are doing a Select Into, which is trying to create another temp table with the same name. If you are using the create table syntax, then your select should look like this insert into #RandomTableName (nameOfObject, NameOfCounter, SampledValue) select vPerformanceRule.ObjectName, vPerformanceRule.CounterName, Perf.vPerfRaw.SampleValue from......

Us the select into when you don't want to create your table ahead of time.

share|improve this answer
    
To be more exact - SELECT INTO creates the table with the exact same field data type definitions as the original table. There is no need to create the table first - nothing is gained in your case. –  TomTom May 31 '12 at 18:16
    
Ah I see the mistake now. Thank you very much. –  Hoser May 31 '12 at 18:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.