Updating an identity column

Updating an identity column

It is a question that often raises passions of almost religious fervour and a search will find many threads on the subject.I have heard people say that every table in a database should have an identity column and that only those should be used in joins.If the table has an identity column then a bulk insert will often fail if the identity values are not held in the text file.The easiest way around this is to create a view on the tabke excluding the identity column and bulk insert into the view.This is not possible and probably not even desirable.Notice that this column actually contains two values – what would be it’s purpose?We create an example table Note one use of an identity.We have inserted duplicate rows but can separate them by the identity column value.

An identity column can be included in a table created using a select into statement via the identity function This is useful for creating a table from existing structures The identity must only contain integer values but the column can be of any numeric datatype (bigint, int, tinyint, numeric, decimal).It is tempting to assume that the identity values will be allocated in the order of rows in the text file but this is not the case.This is often an issue with unstructured data like XML.To deal with such data import into text column (or varchar(max) in v2005 ) then parse the data. You will not be able to define a column or row terminator and there is an interesting “feature” to be aware of in some versions of sql server: If the text file length is divisble by 4 the bulk insert would fail without giving an error.Test your version to see if it has this problem and if so you can check the file length and and a dummy character if it’s divisible by 4.

updating an identity column-39updating an identity column-8updating an identity column-86

Another option is to create a format file to use with the bulk insert.

Join our conversation (37 Comments).
Click Here To Leave Your Comment Updating an identity column.


Leave a Reply

Your email address will not be published. Required fields are marked *