October 8, 2019 at 9:15 pm
This is an oversimplified example of an issue I'm having. I'd like to not change the table #data. The error is the object already exists.
Msg 2714, Level 16, State 1, Line 13
There is already an object named '#Data' in the database.
IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data
IF EXISTS (SELECT 1 WHERE @ProcessId = 1)
BEGIN
SELECT 1 as Test INTO #Data
END
ELSE
IF EXISTS (SELECT 1 WHERE @ProcessId = 2)
BEGIN
SELECT 1 as Test INTO #Data
END
ELSE
IF EXISTS (SELECT 1 WHERE @ProcessId = 3)
BEGIN
SELECT 1 as Test INTO #Data
END
SELECT * FROM #Data
October 8, 2019 at 9:44 pm
In the same module, there can only be one CREATE TABLE/SELECT INTO for the same table. That is just the way it is. It is a holdover from SQL 6.5, which checked that all queries that referred to temp tables were correct with regards to column names etc. That is, there was no deferred name resolution in those days.
If you want a table to have different design depending on different branches in the code, run an initial CREATE TABLE first, and then use ALTER TABLE to add columns. Don't be surprised if you get more errors further down the road.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
October 8, 2019 at 9:47 pm
You're using the lazy method of creating a temp table. Instead of using SELECT/INTO you should be creating the table and then using INSERT/SELECT.
DECLARE @ProcessID TINYINT = 3;
IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data
/* In SQL 2016 and later you can change this to DROP TABLE IF EXISTS #Data */
CREATE TABLE #Data(Test TINYINT);
IF EXISTS (SELECT 1 WHERE @ProcessId = 1)
BEGIN
INSERT #Data (Test)
SELECT 1 as Test
END
ELSE
IF EXISTS (SELECT 1 WHERE @ProcessId = 2)
BEGIN
INSERT #Data (Test)
SELECT 1 as Test
END
ELSE
IF EXISTS (SELECT 1 WHERE @ProcessId = 3)
BEGIN
INSERT #Data (Test)
SELECT 1 as Test
END
SELECT * FROM #Data
Drew
PS: SQL 2008 is no longer supported. You should upgrade your server ASAP.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 8, 2019 at 10:07 pm
Thank you
October 8, 2019 at 10:11 pm
There's nothing per se wrong with CREATEing the table using INTO; indeed, it can help prevent future errors when data types changes, for example. But, you don't want to load the table that way, just create it.
SELECT TOP (0) ...
INTO #data
IF @ProcessId = 1
BEGIN
INSERT INTO #data
SELECT ...
FROM dbo.table1
END /*IF*/
ELSE
IF @ProcessId = 2
BEGIN
INSERT INTO #data
SELECT ...
FROM dbo.table2
END /*IF*/
ELSE
BEGIN
INSERT INTO #data
SELECT ...
FROM dbo.table3
END /*ELSE*/
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply