• Lokesh Vij


    Points: 10836

    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 -->[/url]

    Follow me @Twitter

  • Ron McCullough

    SSC Guru

    Points: 63877

    Nice question for the middle of the week - thank you

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


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

  • demonfox


    Points: 6289

    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: 3331

    Nice question

  • Arthur Olcot


    Points: 6008

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

  • Stuart Davies


    Points: 18878

    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 questionThere 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


    Points: 15941

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

    Sujeet Singh

  • This was removed by the editor as SPAM

  • Raghavendra Mudugal


    Points: 10658

    🙂 (...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)


    Points: 2705

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

  • Andre Guerreiro


    Points: 7319

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

    Best regards,

    Andre Guerreiro Neto

    Database Analyst

  • Toreador


    Points: 11257

    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)


    Points: 2705

    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


    Points: 12292

    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 ( for completeness to explain why 1 and 4 fail while 3 succeeds.

    Otherwise, a pretty good question.

  • Dana Medley


    Points: 6764

    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!

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

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