Home Forums SQL Server 2005 T-SQL (SS2K5) Inserting value for identity column in a temporary table RE: Inserting value for identity column in a temporary table

  • July 7, 2014 at 2:59 am

    #1727294

    kishore1a1216 (7/6/2014)

    If the FROM table ALSO have identity Column then what we do ?

    I am Facing this Problem ..

    SELECT IDENTITY(int, 1, 1) AS 'RowID',* ,ROW_NUMBER() OVER(PARTITION BY UID ORDER BY ID ) AS RN,ROW_NUMBER() OVER(PARTITION BY UID ORDER BY ID)-1 AS RNL

    INTO #temp

    FROM Temp1

    RowId--->Identity IN #temp

    ID------->Identity IN Temp1

    Here Iam Getting The Error like

    Msg 8108, Level 16, State 1, Line 3

    Cannot add identity column, using the SELECT INTO statement, to table '#temp', which already has column 'ID' that inherits the identity property.

    Remove the identity property by casting:

    DROP TABLE #ExistingTable

    CREATE TABLE #ExistingTable (ExistingID int identity(1,1), blah VARCHAR(20))

    INSERT INTO #ExistingTable (blah) VALUES ('first row'), ('second row'),('third row')

    SELECT * FROM #ExistingTable

    DROP TABLE #Temp

    SELECT IDENTITY(int,1,1) AS ReplacementID, ExistingID = CAST(ExistingID AS INT), blah

    INTO #Temp

    FROM #ExistingTable

    SELECT * FROM #Temp

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden