Identity Columns

  • Susantha Bathige

    Ten Centuries

    Points: 1040

    Comments posted to this topic are about the item Identity Columns

    Susantha

  • Iggy-SQL

    SSCarpal Tunnel

    Points: 4169

    Good question, I never thought default values can be used for inserting identity value, I always thought it is meant to be for inserting default value only. I learn something new everyday here 😀


    Urbis, an urban transformation company

  • Marius Els

    Right there with Babe

    Points: 760

    Proof that you can teach an old dog some new tricks, probably depends on the willingness to learn though... thanks for the question

  • Joseph-411461

    Ten Centuries

    Points: 1228

    I thought that the statement should be INSERT INTO ..., but nobody complains about the missing INTO, so I start doubting: can it work without?

  • jim.powers

    SSCrazy Eights

    Points: 8700

    Joseph (7/7/2008)


    I thought that the statement should be INSERT INTO ..., but nobody complains about the missing INTO, so I start doubting: can it work without?

    INTO is an optional keyword. I always use it for readability purposes but it does work without.

  • Marius Els

    Right there with Babe

    Points: 760

    I can concur on the optional "into", but if memory serves me correctly, if you specify the columns to be inserted, one has to include the "into", or is it still optional even specifying columns?

  • Lynn Pettis

    SSC Guru

    Points: 442275

    From BOL regarding the INSERT statement:

    INTO

    Is an optional keyword that can be used between INSERT and the target table.

    😎

  • Marius Els

    Right there with Babe

    Points: 760

    that means irrespective of anything, the "into" is optional.

  • Dariusz Czechowicz-474456

    SSC Enthusiast

    Points: 128

    Here is an alternative solution without 'set identity_insert' 😉

    INSERT INTO TEST WITH (KEEPIDENTITY) (Col1)

    SELECT IDENT_CURRENT('Test') FROM OPENROWSET(BULK N'C:\boot.ini', SINGLE_BLOB) AS a

  • Pires-502779

    SSC Enthusiast

    Points: 120

    Dariusz Czechowicz (7/7/2008)


    Here is an alternative solution without 'set identity_insert' 😉

    INSERT INTO TEST WITH (KEEPIDENTITY) (Col1)

    SELECT IDENT_CURRENT('Test') FROM OPENROWSET(BULK N'C:\boot.ini', SINGLE_BLOB) AS a

    We allways have the hard way to do something!

  • SanjayAttray

    SSChampion

    Points: 13157

    Good one. Always thought insert with insert into..but never thought default values could be used to insert identity value for an identity column.

    SQL DBA.

  • Rudyx - the Doctor

    SSC-Forever

    Points: 43696

    Curte question ... a fairly obvious solution ...

    However, why would one do such a thing ?

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • Pires-502779

    SSC Enthusiast

    Points: 120

    rudy komacsar (7/7/2008)


    Curte question ... a fairly obvious solution ...

    However, why would one do such a thing ?

    I can't see why we would have a table with only an identity, but "default values" is useful when you have a table with defined default values and you are creating data and the default values are suficient for the cenario.

    I think the real lesson where is the "default values" for columns and not the trick for the identity insert.

  • SAinCA

    SSCrazy

    Points: 2071

    If you want to take a one-time hit of about 50 seconds (on my 2008 instance on a Lenovo T61p laptop) for 65000 rows in the ultra-useful "Numbers" table, use the simple Insert in the answer followed by GO 65000 to get an easy 65,000 rows... Drop and recreate with as many or few rows simply with a change to the GO count... Sure beats some of the other "how to populate a Numbers table" scripts for simplicity not performance 😀

    CREATE TABLE [dbo].[Numbers](

    [Number] [int] IDENTITY(0,1) NOT NULL,

    CONSTRAINT [PK_Numbers] PRIMARY KEY CLUSTERED ([Number] ASC) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    INSERT INTO dbo.Numbers DEFAULT VALUES

    go 65000

    SELECT * FROM dbo.Numbers ORDER BY 1

    go

    As it's actually useful to have the Number 0 in the table - start the IDENTITY at ZERO - fell over that in Production - DUH!

  • Anipaul

    SSC-Insane

    Points: 24681

    Excellent one.......:)

Viewing 15 posts - 1 through 15 (of 17 total)

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