Java calls bcp import data to the database to resolve identity column ID issues

Original works, allow reprint, please be sure to use hyperlinks in the form of the original source of the article, author information and this statement. Otherwise, the legal liability will be investigated. http://enetq.blog.51cto.com/479739/912093

A previous blog post explains the call bcp batch export data, for bulk import data is not very detailed, this article detailed introduction of the next use of skills. For bulk import, if the table contains an identity column, the default is to handle the identity column as SQL Server handles it, so this is not the ID value we need, so we'll discuss the workaround here.

。

① the data to be imported is as follows:

650) this.width=650; "border=" 0 "src=" http://img1.51cto.com/attachment/201206/192237788.jpg "style=" padding:0px; Margin:0px;vertical-align:top;border:none; "/>

The red box is the identity column, which automatically grows.

However, we used the

BCP sportSys.dbo.competitions in%1competitions.xls-c-t >>%2import.txt

② the data was imported, there was a problem finding it.

650) this.width=650; "border=" 0 "src=" http://img1.51cto.com/attachment/201206/192341511.jpg "width=" 650 "style=" Padding:0px;margin:0px;vertical-align:top;border:none; "/>

Can be very clear that the ID has changed, the resulting problems can be imagined, how to solve the problem?

The following approach has been proposed:

SET IDENTITY_INSERT TB on--inserts an explicit value into the identity column of the table.  INSERT into ..... SET identity_insert TB off--close option after completion

When this statement is used, it can only import a table and a table, and it loses the meaning of the bulk import.
and writing directly in our BAT file will also prompt

SET identity_insert sportSys.dbo.compet itions on environment variable IDENTITY_INSERT sportSys.dbo.competitions not defined

A review of the documentation found that BCP has provided us with a very good workaround, plus-e

This parameter, you can solve the problem of the identity column!

-e   specifies that identity value or values in the  Imported data file are to be used for the identity column.  If -E is not given, the identity values for this  Column in the data file being imported are ignored, and sql  server automatically assigns unique values based on the seed  and increment values specified during table creation.     If the data file does not contain values for the  Identity column in the table or view, use a format file  to specify that the identity column in the table or  View should be skipped&nbSp;when importing data; sql server automatically assigns unique values  for the column. For more information, see DBCC CHECKIDENT  ( Transact-SQL) .   the -e option has a special permissions  requirement. for more information, see  "Remarks"  later in this  Topic.

If BCP is imported without the-e parameter, the processing of the identity column in the target table is handled automatically by SQL Server, so the resulting ID value is not what we want.

This article is from "Ghost Conan's Technical Blog" blog, please be sure to keep this source http://enetq.blog.51cto.com/479739/912093

Java calls bcp import data to the database to resolve identity column ID issues