Identity Columns

Question

Identity Columns

Susantha Bathige

Ten Centuries

Points: 1040
More actions
July 5, 2008 at 11:05 am

#187799

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

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

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

Iggy-SQL SSCarpal Tunnel Points: 4169 More actions · Answer 1

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: 762 More actions · Answer 2

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 More actions · Answer 3

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 SSCrazy Eights Points: 8718 More actions · Answer 4

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: 762 More actions · Answer 5

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: 442467 More actions · Answer 6

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: 762 More actions · Answer 7

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

Dariusz Czechowicz-474456 SSC Enthusiast Points: 128 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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: 43695 More actions · Answer 11

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 More actions · Answer 12

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: 2228 More actions · Answer 13

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 More actions · Answer 14

Anipaul

SSC-Insane

Points: 24681

July 8, 2008 at 9:27 am

#839564

Excellent one.......:)