IDENTITY INSERT

Question

Post reply

IDENTITY INSERT

Lokesh Vij

SSChampion

Points: 10836
More actions
September 26, 2012 at 9:41 pm

#393262

Comments posted to this topic are about the item IDENTITY INSERT
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions
Link to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
September 27, 2012 at 3:21 am

This was removed by the editor as SPAM

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Nice question for the middle of the week - thank you

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

demonfox SSCertifiable Points: 6289 More actions · Answer 2

nice question ..

I remember someone asked me this question , I was quite confused and my guess was the default ..

apparently when I tested I did work..

thanks for the question..

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Terry300577 Hall of Fame Points: 3391 More actions · Answer 3

Terry300577

Hall of Fame

Points: 3391

September 27, 2012 at 1:11 am

#1542719

Nice question

Arthur Olcot SSCertifiable Points: 6562 More actions · Answer 4

Great question.. I nearly fell into the trap of selecting answer two by not reading the question properly! Thanks.

Stuart Davies SSCoach Points: 19505 More actions · Answer 5

Good question - thanks.

Glad I took time to have the 2nd coffee before answering though

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Divine Flame SSCoach Points: 15943 More actions · Answer 6

Thanks for the nice, easy question :-). I say it easy because I have seen it many times, on SQLSkills.com. Paul Randal has used this (INSERT INTO TableName DEFAULT VALUES) in many of his excellent articles :cool:.

Sujeet Singh

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 7

🙂 (...all that I had)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 8

Thank you for the thought provoking (for me at least) question!

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 9

I still don't get it. What does he mean by "insert the value"? :ermm:

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Toreador SSChampion Points: 11358 More actions · Answer 10

Is it me, or does the "explanation" for (1) just repeat the incorrect syntax used in the question?

"IDENTITY_INSERT is used to set identity of a particular table. SET IDENTITY_INSERT ON/OFF "

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 11

Toreador (9/27/2012)
Is it me, or does the "explanation" for (1) just repeat the incorrect syntax used in the question?
"IDENTITY_INSERT is used to set identity of a particular table. SET IDENTITY_INSERT ON/OFF "

I think the explanation is incomplete. The table name should be specified in the SET INDENTITY_INSERT tablename ON/OFF command.

sknox SSChampion Points: 12388 More actions · Answer 12

1. Query 1 fails because there are no values provided to INSERT.

2. Query 2 fails because the syntax for SET IDENTITY_INSERT requires a table name.

3. Query 3 succeeds because DEFAULT VALUES instructs SQL Server to insert the identity vale.

4. Query 4 fails because VALUES (DEFAULT) does not work for identity columns.

The answer should have included a reference to INSERT (http://msdn.microsoft.com/en-us/library/aa933206(v=sql.80).aspx) for completeness to explain why 1 and 4 fail while 3 succeeds.

Otherwise, a pretty good question.

Dana Medley SSCertifiable Points: 6764 More actions · Answer 13

Thanks for that sknox and thanks for the great question. I nearly fell into the Identity Insert trap and was about to select it before I took a second look and realized what it was missing.

Everything is awesome!