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

General SQL Server Forums

New to SQL Server Programming

Syntax

Author

Topic

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-07-31 : 04:28:32

How come this gives me an error , it says invalid object name T4,
but if i use CREATE TABLE T4 .. that doesn't work either ..

[CODE]
if exists (SELECT * from T4)
DROP TABLE T4

SELECT * into T4 from
(
select id from tab

) as z
[/CODE]

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-31 : 04:32:12

it's because it doesn't exist in your first select (the exist check). what you've written checks for the existance of records, no the table itself

try...
if exists (SELECT 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'T4')

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-31 : 04:33:20

If you use SQL Server 2000, are you using the same owner for the tables?
If you use SQL Server 2005, are you using the same schema for the tables?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-31 : 04:34:22

If all ownership and schema are correct, this should work

IF EXISTS (SELECT * FROM t4)
	DROP TABLE t4

SELECT	*
INTO	t4
FROM	tab

E 12°55'05.25"
N 56°04'39.16"

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-07-31 : 04:40:49

its strange , they dont work together but they work seperately...
if exists ... works on its own .. and so does the insert ...

together though they dont work , it's like it drops T4 and won't recreate it ..

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-07-31 : 04:56:18

OK using the check against the sysobjects table seems to have worked. ..

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-07-31 : 05:46:56

OK i need this to be with temp tables , so now im getting the same original problem ..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 05:50:09

try like this:-

IF EXISTS (SELECT * FROM t4)
DROP TABLE t4
GO

SELECT *
INTO t4
FROM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-31 : 05:51:34

quote:
Originally posted by pazzy11

OK i need this to be with temp tables , so now im getting the same original problem ..

You need to check the existance of temp tables in tempdb database

Madhivanan

Failing to plan is Planning to fail

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-31 : 06:02:21

n Posted - 07/31/2008 : 05:51:34

quote:Originally posted by pazzy11

OK i need this to be with temp tables , so now im getting the same original problem ..

You need to check the existance of temp tables in tempdb database

Madhivanan

Failing to plan is Planning to fail

quote:

How come the table t4 will exsist in tempdb..?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-31 : 06:11:22

quote:
Originally posted by VGuyz

n Posted - 07/31/2008 : 05:51:34

quote:Originally posted by pazzy11

OK i need this to be with temp tables , so now im getting the same original problem ..

You need to check the existance of temp tables in tempdb database

Madhivanan

Failing to plan is Planning to fail
quote:

How come the table t4 will exsist in tempdb..?

Read the part highlighted

Madhivanan

Failing to plan is Planning to fail

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-31 : 06:19:46

sorry madhi,
i agree u'r point.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-31 : 06:21:59

quote:
Originally posted by VGuyz

sorry madhi,
i agree u'r point.

No problem

Madhivanan

Failing to plan is Planning to fail

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-07-31 : 06:30:50

Im checking in tempdb, #T4 exists , i can SELECT * from #T4, but it is not showing in tempdb ???

these wont work ..
[CODE] if exists (SELECT * from tempdb.dbo.sysobjects where name = '#T4')
DROP TABLE #T4
[/CODE]

or
[CODE]
if exists (SELECT * from tempdb.dbo.sysobjects where name = 'T4')
DROP TABLE #T4
[/CODE]

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-31 : 06:33:39

have a look at what the name actually is in tempdb you'll probably find it's something like #T4__________________________ ?? so do a like instead of an =

Em

pazzy11
Posting Yak Master

145 Posts

Posted - 2008-07-31 : 06:36:29

yeah i just did that ,

seems to work when i use the like instead of = ..

Thanks guys ..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-31 : 08:36:38

or

if object_id('tempdb..#T4') is not null
DROP TABLE #T4

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources