Identity Columns

  • Comments posted to this topic are about the item Identity Columns

    Susantha

  • 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

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

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

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

  • 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?

  • From BOL regarding the INSERT statement:

    INTO

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

    😎

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

  • 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

  • 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!

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

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

    However, why would one do such a thing ?

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

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

  • 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!

  • Excellent one.......:)

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

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