Same Table Example

  • texpic

    SSCertifiable

    Points: 5880

    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

     

     

     

     

  • Erland Sommarskog

    SSC-Insane

    Points: 23822

    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]

  • drew.allen

    SSC Guru

    Points: 76634

    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

  • texpic

    SSCertifiable

    Points: 5880

    Thank you

     

  • ScottPletcher

    SSC Guru

    Points: 98174

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply