Post reply

IDENTITY INSERT

  • December 14, 2012 at 3:32 am

    #1568619

    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!

    🙂

  • December 14, 2012 at 7:17 am

    #1568689

    Great Question...

    Thanks

  • December 14, 2012 at 7:21 am

    #1568695

    kapil_kk (12/13/2012)

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

    100% Agreed...

  • December 14, 2012 at 7:48 am

    #1568713

    :-):-P

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • December 14, 2012 at 12:51 pm

    #1568812

    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 😉

  • December 14, 2012 at 2:26 pm

    #1568838

    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.

    Tom

  • December 16, 2012 at 4:32 pm

    #1568972

    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!)

    -dave

  • December 17, 2012 at 3:00 am

    #1569068

    good one:)

  • December 18, 2012 at 2:44 pm

    #1569789

    Good one, thanks!

  • December 19, 2012 at 6:24 am

    #1570031

    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!) 🙂

  • December 19, 2012 at 6:38 am

    #1570038

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

    SET IDENTITY_INSERT TblA1 ON;

    INSERT INTO TblA1

    (col1,

    col2)

    VALUES (7,

    'G');

    SET IDENTITY_INSERT TblA1 OFF;

    GO

    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

    Derek

  • December 21, 2012 at 1:05 pm

    #1571048

    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.

    Anton

  • January 15, 2013 at 1:45 pm

    #1577526

    Thanks for Nice Question

  • September 16, 2015 at 11:04 am

    #1827148

    Nice one.

    Thanks.

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

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