If EXISITS.....DROP Table doesn't work

Hi
Can any of you tell me what I've done wrong in the statement below. The
problem is that when I run the whole statement, it won't drop the table if
it already exist. If I then just run the first 2-3 lines where it looks for
the table and then drop it if it exists, it seems to work.
It might just be me that haven't got the syntax right, but it puzzles me why
it seems to behave differently depending on how much of the statement I run.
The way I have noticed the problem, is that I'm using the same code for 2
databases, where I use 2 different columns in one of the tables. In one
table I use the columns "navn1' and "navn2" where in the other I use
"kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
(and where the temp table doesn't exist already) it works fine and do the
job. Then I change the names of those 2 columns to match the names in the
second database -run the code, and then I get an "Invalid column name
"kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
using the same "version" of the temp table as I created with the first run
of the code.
When I then just execute the first lines of my code where it checks for the
table and drop it if it exist, and then afterwards run the full script, then
it works fine with the new names.
To me that looks like it wont drop the table if it exist, but I might be
wrong?
Additionally I've noticed that temp tables are being named slightly
different if they are local or global temp tables. In this case I'm using a
global temp table and when I run the "SELECT....from
INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
expected. If I use a local temp table it returns the value
"#ejd_adm_temp_____________..." which means that I can't find it in
INFORMATION_SCHEMA.TABLES because it has a different name.
Is this a known issue or is it just me that are missing something on how to
use temp tables (...could very well be the case..:-)...).
Here's the code that I'm running...
--
USE TESTDE
IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = '##ejd_adm_temp')
DROP table ##ejd_adm_temp
select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
, a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email, l.opkrbetalmeddel
INTO ##ejd_adm_temp
FROM lejer l
JOIN ejendom e on l.ejendomnr=e.ejendomnr
JOIN AdrFunk AF on e.ejendomID=AF.recordID
JOIN Adresse A on A.adresseident=AF.adresseident
--
Regards
SteenUse
IF OBJECT_ID('##ejd_adm_temp') IS NOT NULL
For more info see http://www.aspfaq.com/2458
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eSlXExKnEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks
for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me
why
> it seems to behave differently depending on how much of the statement I
run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for
the
> table and drop it if it exist, and then afterwards run the full script,
then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using
a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how
to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email,
l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>|||Use LIKE instead of = with the table name. Temporary tables can and do
have strange names. Anyway, why are you using global temp tables and why
are you trying to drop it.
Adrian
Steen Persson wrote:
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me why
> it seems to behave differently depending on how much of the statement I run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for the
> table and drop it if it exist, and then afterwards run the full script, then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email, l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>|||temp tables are stored in tempdb. The technique is the same regardless of
whether the temp table is a regular temp table or a global temp table.
set nocount on
create table #emp (emp_id int)
create table ##emp (emp_id int)
select object_id('tempdb..#emp')
select object_id('tempdb..##emp')
if object_id('tempdb..#emp') is not null
drop table #emp
if object_id('tempdb..##emp') is not null
drop table ##emp
if object_id('tempdb..#emp') is not null
drop table #emp
else
print '#emp does not exist'
if object_id('tempdb..##emp') is not null
drop table ##emp
else
print '##emp does not exist'
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eSlXExKnEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks
for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me
why
> it seems to behave differently depending on how much of the statement I
run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for
the
> table and drop it if it exist, and then afterwards run the full script,
then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using
a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how
to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email,
l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>|||Hi Adrian
Maybe there's no real reason to drop it. My original reason for dropping the
table, was because I wanted to run this code for both databases/tables in
the same script. Since I then had slightly different column names in the
tables, I had to drop the table in between. I've later changed my mind
though, so I think I'll run it in two seperate jobs and then I don't need to
drop the table.
Also there where no reason for using a global temp table, but I tried it as
a part of my testing where I had problems in dropping the table if it was
already there. Since I could see that my local temp table was named
differently than a global temp table, I tried with the global temp table.
I can easily get around the issue in this case, but I was just puzzled why
it didn't seemed to drop the table when running the whole script when it can
do it if I just run that part of the script. I'm still a learner in SQL , so
I'd like to find out why it behaves like it does - maybe next time I really
need to get it working...:-).
BTW: I also tried using LIKE instead of =, but that gave me the same
problem.
I'll work a bit more with it and also try Aaron's suggestion...
Regards
Steen
Adrian Edwards wrote:
> Use LIKE instead of = with the table name. Temporary tables can and do
> have strange names. Anyway, why are you using global temp tables and
> why are you trying to drop it.
> Adrian
> Steen Persson wrote:
>> Hi
>> Can any of you tell me what I've done wrong in the statement below.
>> The problem is that when I run the whole statement, it won't drop
>> the table if it already exist. If I then just run the first 2-3
>> lines where it looks for the table and then drop it if it exists, it
>> seems to work.
>> It might just be me that haven't got the syntax right, but it
>> puzzles me why it seems to behave differently depending on how much
>> of the statement I run. The way I have noticed the problem, is that
>> I'm using the same code for 2 databases, where I use 2 different
>> columns in one of the tables. In one table I use the columns "navn1'
>> and "navn2" where in the other I use "kontaktnavn1" and
>> "kontaktnavn2". If I then run my code on the first base (and where
>> the temp table doesn't exist already) it works fine and do the job.
>> Then I change the names of those 2 columns to match the names in the
>> second database -run the code, and then I get an "Invalid column
>> name "kontaktnavn1 and "kontaktnavn2. To me that looks like the code
>> it still using the same "version" of the temp table as I created
>> with the first run of the code.
>> When I then just execute the first lines of my code where it checks
>> for the table and drop it if it exist, and then afterwards run the
>> full script, then it works fine with the new names.
>> To me that looks like it wont drop the table if it exist, but I
>> might be wrong?
>> Additionally I've noticed that temp tables are being named slightly
>> different if they are local or global temp tables. In this case I'm
>> using a global temp table and when I run the "SELECT....from
>> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
>> expected. If I use a local temp table it returns the value
>> "#ejd_adm_temp_____________..." which means that I can't find it in
>> INFORMATION_SCHEMA.TABLES because it has a different name.
>> Is this a known issue or is it just me that are missing something on
>> how to use temp tables (...could very well be the case..:-)...).
>>
>> Here's the code that I'm running...
>> --
>> USE TESTDE
>> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
>> WHERE TABLE_NAME = '##ejd_adm_temp')
>> DROP table ##ejd_adm_temp
>> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
>> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email,
>> l.opkrbetalmeddel INTO ##ejd_adm_temp
>> FROM lejer l
>> JOIN ejendom e on l.ejendomnr=e.ejendomnr
>> JOIN AdrFunk AF on e.ejendomID=AF.recordID
>> JOIN Adresse A on A.adresseident=AF.adresseident
>> --
>> Regards
>> Steensql