Select Into SQL statement error

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi, i have the following SQL codes which is to display the output inside a table created. But every time i execute it, it will give me the following message:

There is already an object named '#OutPutTBL' in the database.

Below are my codes:

SQL

DROP TABLE #OutPutTBL

CREATE TABLE #OutPutTBL(TARGETDATE INT, ACTUALDATE INT, PERCENTAGE INT, MONTH VARCHAR(5))
SELECT SUM(CONVERT(INT, TargetDate)) AS TargetDate, SUM(CONVERT(INT, ActualDate)) AS ActualDate, SUM(CONVERT(INT, CompleteRate))/SUM(CONVERT(INT, TargetDate)) AS CompletePercentage, Months  
into #OutPutTBL
FROM #FORMULA
GROUP BY Months

SELECT* FROM #OutPutTBL

Any help would be appreciated.

Posted 1-Jul-14 14:59pm

Jamie888

Add a Solution

Comments

syed shanu 1-Jul-14 21:15pm

Chk like this :

IF OBJECT_ID('tempdb..#OutPutTBL') IS NOT NULL
DROP TABLE #OutPutTBL

CREATE TABLE #OutPutTBL(TARGETDATE INT, ACTUALDATE INT, PERCENTAGE INT, MONTH VARCHAR(5))
SELECT SUM(CONVERT(INT, TargetDate)) AS TargetDate, SUM(CONVERT(INT, ActualDate)) AS ActualDate, SUM(CONVERT(INT, CompleteRate))/SUM(CONVERT(INT, TargetDate)) AS CompletePercentage, Months
into #OutPutTBL
FROM #FORMULA
GROUP BY Months

SELECT * FROM #OutPutTBL

ArunRajendra 1-Jul-14 23:53pm

Try to convert to Table variable instead of temp table. So that you need worry about dropping the table explicitly.

2 solutions

Solution 2

Why don't you try TABLE variables? They are much better then temp tables.

SQL

DECLARE @OutputTBL TABLE
(TARGETDATE INT, ACTUALDATE INT, PERCENTAGE INT, MONTH VARCHAR(5))

INSERT INTO @OutputTBL(TARGETDATE, ACTUALDATE, PERCENTAGE, MONTH)
SELECT SUM(CONVERT(INT, TargetDate)) AS TargetDate, SUM(CONVERT(INT, ActualDate)) AS ActualDate, SUM(CONVERT(INT, CompleteRate))/SUM(CONVERT(INT, TargetDate)) AS CompletePercentage, Months
FROM #FORMULA
GROUP BY Months

SELECT* FROM @OutPutTBL

Posted 1-Jul-14 18:15pm

_Asif_

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jose Pla · Accepted Answer · 2014-07-01T15:17:00

The select into will auto generate your temp table.

either you remove the create table of the temp table or you remove the into part of the select and add an insert into

SQL

DROP TABLE #OutPutTBL
 
--CREATE TABLE #OutPutTBL(TARGETDATE INT, ACTUALDATE INT, PERCENTAGE INT, MONTH VARCHAR(5))
SELECT SUM(CONVERT(INT, TargetDate)) AS TargetDate, SUM(CONVERT(INT, ActualDate)) AS ActualDate, SUM(CONVERT(INT, CompleteRate))/SUM(CONVERT(INT, TargetDate)) AS CompletePercentage, Months  
into #OutPutTBL
FROM #FORMULA
GROUP BY Months
 
SELECT* FROM #OutPutTBL

or remove the select into

SQL

DROP TABLE #OutPutTBL

CREATE TABLE #OutPutTBL(TARGETDATE INT, ACTUALDATE INT, PERCENTAGE INT, MONTH VARCHAR(5))

insert into #OutPutTBL
SELECT SUM(CONVERT(INT, TargetDate)) AS TargetDate, SUM(CONVERT(INT, ActualDate)) AS ActualDate, SUM(CONVERT(INT, CompleteRate))/SUM(CONVERT(INT, TargetDate)) AS CompletePercentage, Months
FROM #FORMULA
GROUP BY Months

SELECT* FROM #OutPutTBL