How to use "set identity_insert off" along with BCP -in

• Anoop P.

  SSCommitted

  Points: 1632

  More actions

  December 28, 2004 at 4:32 am

  #60900

  Hi,

  I am using BCP in opeartion in a shell script for recreating & copying data of many tables. But I noticed that ceratin tables contains "identity" columns, as a result of which the index columns starts its value from 1 (strat seed) while doing BCP -in, rather than the actual value present in the source table.

  I understand that using "set identity_insert  off" property on the tables set the identity property for the column off. But it  happens only for that session.

  I am using the following command for BCP -in inside the shell script.

  bcp $TARGET_DBNAME.dbo.$TABLE_NAME  in $BKP_PATH\\$TABLE_NAME.dat  -C $CODE_PAGE -U $TARGET_LOGIN_NAME  -P $TARGET_DB_PASSWORD  -S $TARGET_DB_SERVER  -n -q >> $LOGFLDR\\BCP_$TABLE_NAME.out

  So wont this be considered as a seperate Session by SQL server. If I fire an OSQL command to set the identity_insert  off, it will be considered as a different session.

  I have tried using

  DBCC CHECKIDENT('Table_name','reseed',new_value)

  But, since the source table are having record numbers like 500,501,502,504,507,....(503, 505 & 506 were deleted as part of maintenance), it becomes impossible to do BCP -in operation with the identity property on. Thats why I am exploring the possibility of setting it off.

  Could anyone please suggest me a solution to overcome the problem.

  Thanking you in Advance,

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  December 28, 2004 at 5:22 am

  #535121

  Have you looked at bcp's -E option?

  /Kenneth

• Anoop P.

  SSCommitted

  Points: 1632

  More actions

  December 29, 2004 at 8:33 am

  #535275

  Hi Kenneth,

  I tried that & its found working.

  Because of this problem I got to learn about SET IDENTITY_INSERT off &

  DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value) properties

  Thanks for your help.