IDENTITY INSERT

  • 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

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

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

  • Nice question

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

  • 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

  • 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

  • This was removed by the editor as SPAM

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

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

  • 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

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

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

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

  • 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