Identity(property)?

• Dave -310292

  Old Hand

  Points: 390

  More actions

  July 9, 2007 at 4:10 pm

  #718129

  Wow,

   

  You guys really can beat a dead horse.  Just kidding!  The reason I am asking is that I have a SQL database one an old server that I am having difficulty migrating to the new server.  The problem is not with the database, but the application that uses it.  I simply am going around the flaws in the application and controlling what I am able to control with the SQL DB. 

  Anyway I used this application to create a new blank db with the same table structures.  I then wanted an easy way to transfer the data from the old server to the new server.  Two things were getting in my way.  One foreign key_primary Key constraints, and Two the identity property. 

   

  I am sure the data is fine once I have inserted all of the information in the new DB.  I was looking for an easy way to disable all constraints and then perform my inserts, and then enable the constraints again.

   

  Thank you all for your helpful suggestions.

• rlively-343684

  SSC Veteran

  Points: 298

  More actions

  July 9, 2007 at 4:23 pm

  #718131

  The easiest way is probably to use one of the tools available for data comparisons such as Apex SQL Diff, Red Gate SQL Data Compare, or xSQL Data Compare, etc (I'm sure there are others).

  But if you don't want to shell out for one of those, then you should be able to write a script to disable all the triggers, constraints, etc then SET IDENTITY_INSERT ON a table, insert all the records, set it back off, and then move to the next table.

  I don't mean to beat an already-dead-horse, but I just didn't see what the huge objection to calling it disabling an identity was. Did you find some documentation on "The reason M$ used IDENTITY_INSERT ON instead of DISABLE_IDENTITY" ? Please don't take my responses as hostile; this is a friendly discussion on semantics.