Cannot use temporary table twice
-
I get the next error message from MS SQL 2000: "There is already an object named '#Teste' in the database."
DECLARE @x int
SET @x = 5
IF @x > 1
SELECT IDField1
INTO #Teste
FROM Table1ELSE
SELECT IDField2
INTO #Teste
FROM Table1(...)
The snippet code above is just an example. I cannot use another temporary table name because the following code must reference #Teste only. Do you know a solution ? Thanks Marcello Turnbull
-
I get the next error message from MS SQL 2000: "There is already an object named '#Teste' in the database."
DECLARE @x int
SET @x = 5
IF @x > 1
SELECT IDField1
INTO #Teste
FROM Table1ELSE
SELECT IDField2
INTO #Teste
FROM Table1(...)
The snippet code above is just an example. I cannot use another temporary table name because the following code must reference #Teste only. Do you know a solution ? Thanks Marcello Turnbull
Where is your create and drop statement for the temp table? Last I heard a temp table was supposed to be local to the scope of the procedure and it was an error to not global to the entire db. Also, you can look into the possibility of using the new table variable type in SQL Server.
Need custom software developed? I do C# development and consulting all over the United States. A man said to the universe: "Sir I exist!" "However," replied the universe, "The fact has not created in me A sense of obligation." --Stephen Crane
-
I get the next error message from MS SQL 2000: "There is already an object named '#Teste' in the database."
DECLARE @x int
SET @x = 5
IF @x > 1
SELECT IDField1
INTO #Teste
FROM Table1ELSE
SELECT IDField2
INTO #Teste
FROM Table1(...)
The snippet code above is just an example. I cannot use another temporary table name because the following code must reference #Teste only. Do you know a solution ? Thanks Marcello Turnbull
You seem to use a local temporary table. When such table is created it exists until you either: - disconnect from the database - drop the table using DROP TABLE command So now you create the table in your SELECT ... INTO statement and if you don't drop it somewhere later, you encounter that error when you run the code block the second time.
The need to optimize rises from a bad design.My articles[^]
-
You seem to use a local temporary table. When such table is created it exists until you either: - disconnect from the database - drop the table using DROP TABLE command So now you create the table in your SELECT ... INTO statement and if you don't drop it somewhere later, you encounter that error when you run the code block the second time.
The need to optimize rises from a bad design.My articles[^]
I agree there is no DROP statement but it is not enough to fix the problem. Thanks Marcello Turnbull
-
I agree there is no DROP statement but it is not enough to fix the problem. Thanks Marcello Turnbull
Don't quite understand. Your problem is that you try to create a table that already exists. So you must either drop it or modify your code so that you don't create the table if it already exists.
The need to optimize rises from a bad design.My articles[^]
-
Don't quite understand. Your problem is that you try to create a table that already exists. So you must either drop it or modify your code so that you don't create the table if it already exists.
The need to optimize rises from a bad design.My articles[^]
After you answer I added the DROP TABLE in the beginning of the code but it has not worked. I must not modify the code following the snippet. Thank you Marcello Turnbull
-
After you answer I added the DROP TABLE in the beginning of the code but it has not worked. I must not modify the code following the snippet. Thank you Marcello Turnbull
-
Could you post the whole code? Also do you receive the same error or something else?
The need to optimize rises from a bad design.My articles[^]
-
That's odd. For some reason the parser interpets that the table is going to be created twice (of course that's not gonna happen since you have IF ... ELSE structure). However, could you use the following:
DROP TABLE #Teste;
CREATE TABLE #Teste (
IDField1 int
);
DECLARE @x int;
SET @x = 5;
IF (@x > 1)
BEGIN
INSERT INTO #Teste
SELECT IDField1
FROM Table1;
END;
ELSE
BEGIN
INSERT INTO #Teste
SELECT IDField1
FROM Table1;
END;The need to optimize rises from a bad design.My articles[^]
-
That's odd. For some reason the parser interpets that the table is going to be created twice (of course that's not gonna happen since you have IF ... ELSE structure). However, could you use the following:
DROP TABLE #Teste;
CREATE TABLE #Teste (
IDField1 int
);
DECLARE @x int;
SET @x = 5;
IF (@x > 1)
BEGIN
INSERT INTO #Teste
SELECT IDField1
FROM Table1;
END;
ELSE
BEGIN
INSERT INTO #Teste
SELECT IDField1
FROM Table1;
END;The need to optimize rises from a bad design.My articles[^]
-