March 12, 2010 at 11:51 pm
I have a stored procedure that is as follows:
[font="Courier New"]
select customer_id
INTO #T1
FROM Customer
WHERE zip_code = '92646'
update customer
set local_fl = 1
from customer
inner join #T1 on customer.customer_id = #T1.customer_id
drop table #T1
select customer_id
INTO #T1
FROM Customer
WHERE zip_code <> '92646'
update customer
set local_fl = 0
from customer
inner join #T1 on customer.customer_id = #T1.customer_id
drop table #T1
[/font]
I know there are easier ways to do the above code, however, the above code is a sample. I get an error that "There is already an object named '#T1' in the database." So what am I doing wrong?
Thanks,
Mike
March 12, 2010 at 11:57 pm
There is an temporary table in the database already that all, where is the create statement in SP? run drop table statement before creating temp table from store procedure.
March 13, 2010 at 12:22 am
Sorry, I should have stated that the error occurs on the 2nd SQL statement (after the 1st drop table).
Mike
March 13, 2010 at 12:34 am
Maybe I'm missing something, but that entire piece can be one update statement
update customer
set local_fl = CASE zip_code WHEN '92646' THEN 1 ELSE 0 END
Does that work? I honestly can't see a need for temp tables at all in your script.
The error has to do with parsing, the parser doesn't execute the code and sees the same table been created twice, hence the error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 13, 2010 at 12:46 am
Here is a code block that can duplicate the problem. I know it is a simple example, however, if I can understand how to make this example work, I can apply it to my much bigger problem.
[font="Courier New"]
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Customer','U') IS NOT NULL
DROP TABLE #customer
--===== Create the test table with
CREATE TABLE #Customer
(
customer_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
name char(30),
zip_code char(5),
local_fl bit,
btype_id int,
size_id int)
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Btype','U') IS NOT NULL
DROP TABLE #BType
--===== Create the test table with
CREATE TABLE #Btype
(
btype_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
btype char(30)
)
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#size','U') IS NOT NULL
DROP TABLE #Size
--===== Create the test table with
CREATE TABLE #size
(
size_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Size char(30)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #BType off
--===== Insert the test data into the test table
INSERT INTO #Btype
(btype_id, btype)
SELECT '1','LLC' UNION ALL
SELECT '2','CORP' UNION ALL
SELECT '3','SOLE' UNION ALL
SELECT '4','S-CORP'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #BType ON
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Size off
--===== Insert the test data into the test table
INSERT INTO #Size
(size_id,Size)
SELECT '1','Small (1-5)' UNION ALL
SELECT '2','Med (6-100)' UNION ALL
SELECT '3','Large (101-999)' UNION ALL
SELECT '4','Huge (1000+)'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #Size ON
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Customer off
--===== Insert the test data into the test table
INSERT INTO #Customer
(customer_ID,name,zip_code,btype_id,size_id,local_fl)
SELECT '1', 'Blue Shoes', '92646', 1, 1, 0 UNION ALL
SELECT '2', 'Red Pants', '92646', 2, 3, 0 UNION ALL
SELECT '3', 'Subs R Us', '92648', 3, 1, 0 UNION ALL
SELECT '4', '.Com Builder', '80011', 2, 4, 0
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #Customer ON
SELECT #Customer.*
INTO #CTemp
FROM #Customer
WHERE #Customer.zip_code = '92646'
UPDATE #Customer
SET local_fl = 1
FROM #Customer
INNER JOIN #CTemp ON #Customer.customer_ID = #cTemp.customer_id
DROP TABLE #CTemp
SELECT #Customer.*
INTO #CTemp
FROM #Customer
WHERE #Customer.zip_code <> '92646'
UPDATE #Customer
SET local_fl = 0
FROM #Customer
INNER JOIN #CTemp ON #Customer.customer_ID = #cTemp.customer_id
DROP TABLE #CTemp
[/font]
When run, I get the following 'messages:'
[font="Courier New"]
Msg 2714, Level 16, State 1, Line 96
There is already an object named '#CTemp' in the database.
[/font]
Thanks for the help!
Mike
March 13, 2010 at 1:01 am
In general, use two different temp tables or, instead of dropping the temp table, truncate it and change the second select into to an insert into.
SELECT #Customer.*
INTO #CTemp
FROM #Customer
WHERE #Customer.zip_code = '92646'
UPDATE #Customer
SET local_fl = 1
FROM #Customer
INNER JOIN #CTemp ON #Customer.customer_ID = #cTemp.customer_id
DROP TABLE #CTemp
SELECT #Customer.*
INTO #CTemp2
FROM #Customer
WHERE #Customer.zip_code <> '92646'
UPDATE #Customer
SET local_fl = 0
FROM #Customer
INNER JOIN #CTemp2 ON #Customer.customer_ID = #cTemp2.customer_id
DROP TABLE #CTemp2
Or
SELECT #Customer.*
INTO #CTemp
FROM #Customer
WHERE #Customer.zip_code = '92646'
UPDATE #Customer
SET local_fl = 1
FROM #Customer
INNER JOIN #CTemp ON #Customer.customer_ID = #cTemp.customer_id
TRUNCATE TABLE #CTemp
INSERT INTO #CTemp
SELECT *
FROM #Customer
WHERE #Customer.zip_code <> '92646'
UPDATE #Customer
SET local_fl = 0
FROM #Customer
INNER JOIN #CTemp ON #Customer.customer_ID = #cTemp.customer_id
DROP TABLE #CTemp
That said, again, in this case there's no need whatsoever for those temp tables, a single update with a case statement will work and will be quicker.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 13, 2010 at 3:43 am
Mike,
Hoping that it will be helpful to you, I have tidied up your script and implemented Gail's suggestion for the UPDATE.
-- Drop any existing test tables
IF OBJECT_ID(N'tempdb..#Customer', N'U')
IS NOT NULL
DROP TABLE #Customer;
IF OBJECT_ID(N'tempdb..#Btype', N'U')
IS NOT NULL
DROP TABLE #BType;
IF OBJECT_ID(N'tempdb..#Size', N'U')
IS NOT NULL
DROP TABLE #Size;
-- Create test tables
CREATE TABLE #Customer
(
customer_id INTEGER IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
name VARCHAR(30) NOT NULL,
zip_code CHAR(5) NOT NULL,
local_fl BIT NOT NULL,
btype_id INTEGER NOT NULL,
size_id INTEGER NOT NULL,
);
CREATE TABLE #Btype
(
btype_id INTEGER IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
btype VARCHAR(30) NOT NULL,
);
CREATE TABLE #Size
(
size_id INTEGER IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
size VARCHAR(30) NOT NULL,
);
-- Allow INSERTs to the IDENTITY column
SET IDENTITY_INSERT #BType ON;
-- Sample data
INSERT #Btype
(btype_id, btype)
SELECT 1, 'LLC' UNION ALL
SELECT 2, 'CORP' UNION ALL
SELECT 3, 'SOLE' UNION ALL
SELECT 4, 'S-CORP';
-- Reset IDENTITY INSERT
SET IDENTITY_INSERT #BType OFF;
-- #Size table
SET IDENTITY_INSERT #Size ON;
INSERT #Size
(size_id,Size)
SELECT 1, 'Small (1-5)' UNION ALL
SELECT 2, 'Med (6-100)' UNION ALL
SELECT 3, 'Large (101-999)' UNION ALL
SELECT 4, 'Huge (1000+)';
SET IDENTITY_INSERT #Size OFF;
SET IDENTITY_INSERT #Customer ON;
INSERT #Customer
(customer_ID,name,zip_code,btype_id,size_id,local_fl)
SELECT 1, 'Blue Shoes', '92646', 1, 1, 0 UNION ALL
SELECT 2, 'Red Pants', '92646', 2, 3, 0 UNION ALL
SELECT 3, 'Subs R Us', '92648', 3, 1, 0 UNION ALL
SELECT 4, '.Com Builder', '80011', 2, 4, 0;
SET IDENTITY_INSERT #Customer OFF;
-- Replaces all the previous code to update
-- the 'local to Florida' flag
UPDATE #Customer
SET local_fl =
CASE
WHEN zip_code = '92646' THEN 1
ELSE 0
END;
SELECT C.customer_id,
C.name,
C.zip_code,
C.local_fl,
C.btype_id,
C.size_id
FROM #Customer C;
DROP TABLE
#Customer,
#BType,
#Size;
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 13, 2010 at 4:00 am
I'm not sure if I'm missing the point but to me it seems more like the question is:
Why is the DROP TABLE statement not recognized?
As far as I know you cannot define a table twice within a batch, even if there is a drop table statement in between since the query is evaluated during parse time and checked for duplicate table creation. But I'm not sure about that.
To reuse the same temp table the statement needs to be separated in two batches:
--this will work
CREATE TABLE #temp ( id INT )
DROP TABLE #temp
GO
CREATE TABLE #temp ( id INT )
--but this won't
CREATE TABLE #temp ( id INT )
DROP TABLE #temp
CREATE TABLE #temp ( id INT )
However, using separate temp table within one code block is a more clean method. The same goes for TRUNCATE instead of DROP for the given scenario. Like Gail already said.
March 13, 2010 at 4:24 am
GilaMonster (3/13/2010)
The error has to do with parsing, the parser doesn't execute the code and sees the same table been created twice, hence the error.
Lutz...:laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 13, 2010 at 4:35 am
Paul White (3/13/2010)
GilaMonster (3/13/2010)
The error has to do with parsing, the parser doesn't execute the code and sees the same table been created twice, hence the error.Lutz...:laugh:
:blush:
March 13, 2010 at 8:41 am
Thank you to all who contributed. I will try the truncate table idea.
Mike
March 15, 2010 at 2:07 pm
Best Practices covers most of the stuff:
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply