0

We have a case where we need to disable and enable identity for multiple tables in a single session for Sybase. I have been trying to find a solution, but am unable to find a satisfactory one so far. Solutions such as creating new table/column without identity, copying data, then dropping old table/column is costly, since the source table may contain huge volume of data. Also SET IDENTITY_INSERT ON/OFF can be performed for only a single table in a session and hence this won’t work as well.

Any suggestions? Sybase version used is: Adaptive Server Enterprise/15.7/EBF 21148 SMP SP100 /P/RS6000/AIX 6.1/ase157sp100/3426/64-bit/FBO/

P.S: We had similar case for SQL Server. It was done through ALTER TABLE ... SWITCH command as suggested here -> Adding an identity to an existing column. Anything similar for Sybase should work also.

Community
  • 1
  • 1
arg
  • 61
  • 4
  • 1
    Can you mention the reason why you want to enable/disable identity insert on multiple tables? – Meet Oct 09 '14 at 13:12
  • Use case is to move data from source to target, including the identity columns. This is because the identity columns participate in pks and fks. Since there are multiple tables with identity, we need to disable the identity property on all such tables – arg Oct 10 '14 at 04:16
  • 1
    you can use sybase ASE `bcp` utility to bulk insert. Use `-E` option to copy identity values. Not putting `-E` will ignore identity values from file (DB server will generate the values in the table). BCP [link] (http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc30191.1550/html/utility/X14951.htm) – Meet Oct 13 '14 at 06:52

0 Answers0