SELECT INTO ignoring IDENTITY column

  • Hello,

    I want to know if anyone knows of an easy way to do a SELECT INTO from a table with an identity column, but I do not want the Identity column to be marked as an identity column in the new table. I do want the data from the identity column, but I don't want to have the identity property.

    The best I can come up with is using the union operator:

    with t1 as

    (select * from table union select * from table)

    select * into newtable from t1

  • USE ProofOfConcept;

    GO

    CREATE TABLE dbo.T1

    (ID INT IDENTITY

    PRIMARY KEY,

    Col1 CHAR(1) NOT NULL);

    GO

    INSERT INTO dbo.T1

    (Col1)

    VALUES ('A');

    GO

    SELECT CAST(ID AS INT) AS ID,

    Col1

    INTO dbo.T2

    FROM dbo.T1;

    GO

    SELECT *

    FROM sys.columns AS C

    WHERE C.object_id = OBJECT_ID(N'dbo.T2');

    GO

    DROP TABLE dbo.T1;

    DROP TABLE dbo.T2;

    Wrap the identity column in a Cast/Convert to its native datatype. NULLability, etc., will also revert to default, but that can be fixed easily (if desired) by Alter Table scripts after the Select Into.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I'm sorry, I should've mentioned I am deliberately trying to avoid listing out columns. Trying to see if there is a way to populate several QA tables from a production database without having to update this code everytime they update the table. I will probably have to end up doing dynamic sql, but I want to make sure I explore all options first.

  • You'll need to do it with dynamic SQL, in that case. Pretty easy to select the column names from sys.columns and go from there.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • This should "turn off" the identity property:

    SELECT *

    INTO dbo.newtable

    FROM dbo.table

    UNION ALL

    SELECT TOP 1 (*)

    FROM dbo.table

    WHERE 1 = 0

    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".

  • ScottPletcher (5/21/2013)


    This should "turn off" the identity property:

    SELECT *

    INTO dbo.newtable

    FROM dbo.table

    UNION ALL

    SELECT TOP 1 (*)

    FROM dbo.table

    WHERE 1 = 0

    Nice one, Scott! Need to remember this.

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • Jan Van der Eecken (5/22/2013)


    ScottPletcher (5/21/2013)


    This should "turn off" the identity property:

    SELECT *

    INTO dbo.newtable

    FROM dbo.table

    UNION ALL

    SELECT TOP 1 (*)

    FROM dbo.table

    WHERE 1 = 0

    Nice one, Scott! Need to remember this.

    Agreed. Thanks Scott

Viewing 7 posts - 1 through 6 (of 6 total)

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