Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

KEEPIDENTITY in SQL SERVER

Oct 7 2011 5:11AM by Robert Dennyson   

KEEPIDENTITY applicable only in an INSERT statement when the BULK option is used with OPENROWSET. Specifies that the identity value or values in the imported data file are to be used for the identity column. If KEEPIDENTITY is not specified, the identity values for this column are verified but not imported and the query optimizer automatically assigns unique values based on the seed and increment values specified during table creation.

Here is the Example

INSERT INTO HumanResources.myDepartment
   with (KEEPIDENTITY)
   (DepartmentID, Name, GroupName, ModifiedDate)
   SELECT *
      FROM  OPENROWSET(BULK 'C:\myDepartment-n.Dat',
      FORMATFILE='C:\myDepartment-f-n-x.Xml') as t1;
Read More..   [13 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


Robert Dennyson
11 · 14% · 4420
19
 
2
 
17
 
0
Incorrect
 
 
0
Forgotten



Submit

6  Comments  

  • This make me think of SET IDENTITY_INSERT (http://msdn.microsoft.com/fr-fr/library/ms188059.aspx).

    commented on Oct 7 2011 5:22AM
    Sergejack
    41 · 4% · 1395
  • Yep, So close to it.

    commented on Oct 7 2011 5:39AM
    Robert Dennyson
    11 · 14% · 4420
  • Yes, this can be done with SET IDENTITY_INSERT TABLENAME ON and OFF settings also. But this is new to known. Thanks.

    commented on Oct 7 2011 5:39AM
    Paresh Prajapati
    6 · 23% · 7444
  • All: SET IDENTITY_INSERT ON is dangerous, and is not the same thing as KEEPIDENTITY. Here's why:

    Per Books-On-Line (http://msdn.microsoft.com/en-us/library/ms188059.aspx): "If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value."

    Please be careful - no ISV will provide two alternate methods to do the same thing, with the same results & side-effects. In this case, KEEPIDENTITY and SET IDENTITY_INSERT ON appear to be the same thing, but are not.

    commented on Oct 8 2011 8:56AM
    Nakul Vachhrajani
    4 · 36% · 11622
  • I don't get why that makes it dangerous, Nakul. Plus, this behavior is to be expected.

    commented on Oct 10 2011 2:15AM
    Sergejack
    41 · 4% · 1395
  • For one, if SET IDENTITY_INSERT is left ON, we would end up in problems. I have seen this blowing up in the face a couple of times when performed by relatively inexperienced developers, or when they are in a hurry.

    I guess the main argument is to modify a table's properties only for the required duration. During BULK operations, if the KEEPIDENTITY is used, the setting takes effect only for the duration of the query, and no more.

    commented on Oct 10 2011 11:22AM
    Nakul Vachhrajani
    4 · 36% · 11622

Your Comment


Sign Up or Login to post a comment.

"KEEPIDENTITY in SQL SERVER" rated 5 out of 5 by 19 readers
KEEPIDENTITY in SQL SERVER , 5.0 out of 5 based on 19 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]