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

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.