T-SQL Syntax - 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 2000 Forums

Transact-SQL (2000)

T-SQL Syntax

Author

Topic

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-13 : 20:10:23

Hi All,


DECLARE @A AS INT
SET @A=1

IF @A=0
SELECT  * INTO #X FROM RESOURCE
ELSE
SELECT * INTO #X FROM RESOURCE WHERE RESOURCEID=1

I am getting error:
There is already an object named '#X' in the database.

Is there any way to do this without using dynamic sql.

Thanks

mk_garg

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-13 : 20:12:24

Not sure what dynamic sql has to do with this, but:

DECLARE @A AS INT
SET @A=1

CREATE TABLE #X
(
...
)

IF @A=0
INSERT INTO #X SELECT * FROM RESOURCE
ELSE
INSERT INTO #X SELECT * FROM RESOURCE WHERE RESOURCEID=1

Tara

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-13 : 20:22:06

Thanks Tara!

mk_garg

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-13 : 22:47:09

you'll hit this error again if you rerun the code using the same connection, be sure to check if #X is existing if yes then skip create or you can drop and recreate.

just a thought...

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-14 : 04:15:44

Thanks for that tip.
Most of time when i create temp tables.
I drop them when they are not required.

mk_garg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources