SELECT INTO giving ERROR - 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

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT INTO giving ERROR

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-08-08 : 07:17:05

Hello All,

I am trying to create and insert records in temp table based on the IF condition. But it's failing.

ERROR:
Msg 2714, Level 16, State 1, Line 11
There is already an object named '#tbl1' in the database.


 if (select count(*) from tbl WHERE ORGID = 12) > 0
 begin
      SELECT * INTO #tbl1
      FROM ORGtbl
end   
else
begin
      SELECT * INTO #tbl1
      FROM tbl     
end

select  * from #tbl1

Thanks,
-P

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-08 : 07:26:38

thats because each time you run the query its trying to create table #tbl1 so if it already exists it will throw this error.
just add this statement at end to avoid the error

DROP TABLE #tbl1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-08 : 07:50:59

That happens because of the parser. Even if there's no existing table #tbl1, the parser sees two 'create table' statements. To the parser it doesn't matter that they're in an IF, it doesn't evaluate conditionals in any way.

Create the table first, then use the IF to populate it with.

CREATE TABLE #tbl1 (
.. definition here ..
) 

if EXISTS (select 1 from tbl WHERE ORGID = 12) 
 begin
      INSERT INTO #tbl1
      SELECT * FROM ORGtbl
end   
else
begin
      INSERT INTO #tbl1
      SELECT * FROM tbl     
end

select  * from #tbl1

--
Gail Shaw
SQL Server MVP

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2011-08-08 : 08:24:01

visakh16 - I get this error in stored procedure not just in T-SQL code. This was just the piece of code I was having issues with.

Gila - There are so many columns 30+ so I was avoiding create table statement. But I guess I have no choice now.

Thanks,
-P

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-08 : 08:45:05

No, you don't have a choice, and you shouldn't pick the lazy route anyway.

--
Gail Shaw
SQL Server MVP

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-08 : 08:51:41

If you don't want to type in the column names, you can slightly modify Gail's example like this:

SELECT top 0 * into #tbl1 FROM tbl

if EXISTS (select 1 from tbl WHERE ORGID = 12) 
 begin
      INSERT INTO #tbl1
      SELECT * FROM ORGtbl
end   
else
begin
      INSERT INTO #tbl1
      SELECT * FROM tbl     
end

select  * from #tbl1

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-08-10 : 14:42:48

quote:
if (select count(*) from tbl WHERE ORGID = 12) > 0
begin
SELECT * INTO #tbl1
FROM ORGtbl
end
else
begin
SELECT * INTO #tbl1
FROM tbl
end

select * from #tbl1

How about:

if exists(select * from tbl WHERE ORGID = 12) SELECT * FROM ORGtbl
else SELECT * FROM tbl

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources