sybase bcp and identity column

bcp and identity columns are reliable, proven features, but together they surprised me — if I bcp out a table to a file, wipe out the table, then bcp in, occasionally, i see the identity values completely changed. I think it’s because bcp-in ignored identity.

Solution1: use a tool like aqua studio to generate the inserts statements (hopefully not 200,000,000 rows), then enable identity-insert

Solution2: Sybase site says — By default, when you bulk copy data into a table with an IDENTITY column, bcp assigns each row a temporary IDENTITY column value of 0. As it inserts each row into the table, the server assigns it a unique, sequential IDENTITY column value, beginning with the next available value. To enter an explicit IDENTITY column value for each row, specify the -E (UNIX) flag. Refer to the Utility Guide for your platform for more information on bcp options that affect IDENTITY columns.


Fill in your details below or click an icon to log in: 徽标

您正在使用您的 账号评论。 登出 /  更改 )

Google photo

您正在使用您的 Google 账号评论。 登出 /  更改 )

Twitter picture

您正在使用您的 Twitter 账号评论。 登出 /  更改 )

Facebook photo

您正在使用您的 Facebook 账号评论。 登出 /  更改 )

Connecting to %s