SELECT INTO TEMP TABLE - 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

SELECT INTO TEMP TABLE

Author

Topic

iDev
Starting Member

8 Posts

Posted - 2007-04-17 : 06:15:00

The following batch does not compile. It works for real tables but not temp tables. I need to get this to work. Any ideas? Thanks.

code
-------------------
IF 1 = 1 --IF CONDITION
BEGIN
SELECT * INTO #TEMP FROM TABLE1
END
ELSE
BEGIN
SELECT * INTO #TEMP FROM TABLE2
END
--------------------
Error Msg
Msg 2714, Level 16, State 1, Line 7
There is already an object named '#TEMP' in the database.
--------------------

bpgupta
Yak Posting Veteran

75 Posts

Posted - 2007-04-17 : 06:22:07

open new query analyser and copy and paste the query in new window and run it , i think it will run there in new instance of window

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 06:23:49

No, it will not work even for "real" tables.
Please post REAL code and we will be able to suggest.

Peter Larsson
Helsingborg, Sweden

iDev
Starting Member

8 Posts

Posted - 2007-04-17 : 07:57:27

Thank you all for your replies.

Here is the compilable code snippet
---------------------------------------------------------
IF EXISTS(SELECT * FROM TEMPDB.SYS.TABLES WHERE NAME LIKE '#TEMP%')
DROP TABLE #TEMP

IF 1=0 --[IF CONDITION]
BEGIN
CREATE TABLE #TEMP
(
ID INT
)
END
ELSE
BEGIN
CREATE TABLE #TEMP
(
NAME VARCHAR(10)
)
END
---------------------------------------------------------
I read the following from MSDN books online: "If more than one temporary table is created inside a single stored procedure or batch, they must have different names." I assume that means that the batch above is invalid, even though the create #temp stmts are separated with a mutually exclusive IF-condition.

Any ideas?. Thanks again for your response.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 07:59:08

The within the "check 1 = 0" block will never occur.

Peter Larsson
Helsingborg, Sweden

iDev
Starting Member

8 Posts

Posted - 2007-04-17 : 08:02:17

Please run the code snippet I posted above, and you will get the following error
------------------------------------------------------------
Msg 2714, Level 16, State 1, Line 14
There is already an object named '#TEMP' in the database.

Thanks again.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-17 : 08:07:51

Yes. This is standard behavior. Using different temp table names is the only solution as MS documentation says.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

iDev
Starting Member

8 Posts

Posted - 2007-04-17 : 08:09:59

Hi Peso,

Thanks for responding. Yeah the (1=0) check is just for convenience to allow me to illustrate my problem in a somewhat more simplified manner.

You will find that even if you replace (1=0) with a real condition, the code will still not compile; however, if you were to replace the temp table with a REAL table, the code will compile.

Any ideas?

Thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 08:15:38

I see now. There is no "loop" as stated in the comments. It is just an ordinary check.

Peter Larsson
Helsingborg, Sweden

bpgupta
Yak Posting Veteran

75 Posts

Posted - 2007-04-17 : 08:19:15

In one proc using same name Temp table is not allowed.
write a sub proc which will create temp table and return the value as u using condition for checking in If clause and use the return values in Parent Sp to create the Temp table with same name with chekcing condition

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 08:26:50

Here is a work-around

CREATE TABLE #TEMP (<common columns from Table1 and Table2>

IF 1 = 1 -- IF CONDITION
	INSERT INTO #TEMP SELECT <col-list> FROM TABLE1
ELSE
	INSERT INTO #TEMP SELECT <col-list> FROM TABLE2

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-17 : 08:31:39

Another good reason for not using SELECT...INTO dialect!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-04-17 : 12:02:11

I think you can have


CREATE TABLE #TEMP
(
...
)

...

DROP TABLE #TEMP

...

CREATE TABLE #TEMP
(
...
)

so creating the table in the first style, and then Dropping and Recreating in the Second Style, might be a workaround.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources