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.

Leave a Reply

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

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s