• I like this question, as you can work it out with a bit of thought but it doesn't take all day. And I learnt something new. Thank you!


  • Great Question...


  • kapil_kk (12/13/2012)

    we always learn new things from SqlServer central :-):-)

    100% Agreed...

  • :-):-P

    To get quick answer follow this link:

  • Curious about this one, wouldn't the second SET IDENTITY_INSERT ON statement generate an error right there?

    "If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for."

    Just trying to improve my skills 😉

  • Good question.

    I managed to misread the code and got it wrong (with a "none of the above answer", because I thought the firsttwo inserts were both to the first table, instead of one to each table). I used to spend a lot of time telling people they should always read code carefully, never skim-read, but it seems as I get older I'm more and more often not taking my own advice.


  • harmiewiz (12/14/2012)

    Curious about this one, wouldn't the second SET IDENTITY_INSERT ON statement generate an error right there?

    Yes it does 🙂

    and if it weren't for all those GO's in the code then it would stop in its tracks, but they allow for the remainder of the code to execute post error.

    (i'm sure there is a much better explanation about what is actually happening, but I have a meeting to get to!)


  • good one:)

  • Good one, thanks!

  • Good question..

    But it was a mental note to-self as I looked at the code about the SET IDENTITY_INSERT TblA1 ON; etc, and remebered I come across the IDENTITY_INSERT on/off thing in the past, so tweaked it..

    So in future when I look at these questions.. Read the SQL and don't re-write unless you are asked to! (doh!) 🙂

  • I correctly remember that only one table can have IDENTITY_INSERT ON, but forgot that it doesn't automatically switch to a new table. Hence the habitual code ...





    VALUES (7,




    I.e. turn it off in the same script as you turn it on!

    That's the trouble with habits - you forget some of the details of why you always do it that way!:-D


  • The best reason for doing the questions of the day is when you get one wrong, you learn quite a bit.

    Thanks for the question and the follow up responses.


  • Thanks for Nice Question

  • Nice one.


Viewing 14 posts - 31 through 43 (of 43 total)

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