We help IT Professionals succeed at work.

IF NOT EXISTS and SELECT INTO

jport88
jport88 asked
on
299 Views
Last Modified: 2012-05-07
How come when I have the following statement twice in one SQL batch, it gives me an error.  If I open an query analyzer and run them one at a time (maintaining an open connection to preserve temp table) it does not give me an error.

This code is simplified for this post - but there is an automated set of routines which generate the SQL scripts and so this TempTable could be created multiple times.  To avoid that, I want to check if it exists.  

Why will these not work when run together but will work fine when run separately (again in same connection so the second statement actually sees that the first created the table).

The error I get states:
There is already an object named '#TEMP_NAMEHERE' in the database.

Any help is appreciated.

IF NOT EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#TEMP_NAMEHERE____%') BEGIN SELECT [FIELD] AS KEYVALUE INTO [#TEMP_NAMEHERE] FROM Database..Table WHERE WhereClause END
 
 IF NOT EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#TEMP_NAMEHERE____%') BEGIN SELECT [FIELD] AS KEYVALUE INTO [#TEMP_NAMEHERE] FROM Database..Table WHERE WhereClause END

Open in new window

Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
try this way
IF OBJECT_ID('tempdb..#TEMP_NAMEHERE') IS NOT NULL
  BEGIN SELECT [FIELD] AS KEYVALUE INTO [#TEMP_NAMEHERE] FROM Database..Table WHERE WhereClause END

Author

Commented:
Still had issue.  I think the issue may actually be that SQL Server does not allow thre same TEMP table to be created in a SQL batch more than once: http://www.informit.com/articles/article.aspx?p=25288&seqNum=4

Meaning I would need to not store it as a TEMP table.

Does that make sense?
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
why dont you create the table first and insert data

IF OBJECT_ID('tempdb..#TEMP_NAMEHERE') IS NOT NULL
create table #Temp_NameHere (Field int  )
 insert INTO [#TEMP_NAMEHERE]
 SELECT [FIELD] FROM Database..Table WHERE WhereClause
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
You can try dropping that #temp table if you want to create it several times right.

if exists ( select * from information_schema.tables where table_name = '#temp')
drop table #temp
go

Now include this wherever you need to create that temp table.

Hope this helps
Consultant
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Ignore my earlier script..

Use this one to drop #temp table before it is created again:

if OBJECT_ID('tempdb..#test') is not NULL
DROP TABLE #temp
go
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.