Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    27

    Unanswered: Copying non-uniform identity fields

    Greetings all,

    I have 2 tables with the exact table definition in both my production and development databases. Each table has a identity numeric field, however the identity is not uniform thru out the table especially in the production database. How can I copy the exact data from the production table to the development table while keeping the exact identity field values? Any help on this would be appreciated.

    Sincerely,

    Patrick Quinn

    TQ3Navigant Sybase Database Administrator

  2. #2
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    Patrick

    Ah, so you are now forced to deal with the consequences of lazy developers, yet again. Identity columns are great for prototyping but they have no place in a table that is supposed to be in "test" or "production", that is a "table" that :
    (a) exists in a "database" or
    (b) needs administration (replication, migration).
    Basically, the key (identity column value) is not hard, it is made of used chewing gum, and you will suffer the indignity of moving somone else's warm, wet gum around your desk. If the keys are not "uniform", or reliable, they are not keys in the relational sense. Wait until you start to deal with the identity gaps.

    To get started, you can try
    Code:
    bcp -E
    But watch out for unintentionally propagating the foreign key problems.

    Cheers
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  3. #3
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Personally I disagree with Derek - identity columns can be managed quite effectively, in particular with current tools (identity_gap attribute, identity_update option, etc).

    In your case you can use "set identity_insert <tablename> on" and then insert the data from the production table - as long as the values being inserted don't collide with the values already in the table.

    Michael

  4. #4
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    The point is that you CAN load/unload/migrate a table with hard logical keys (not identity), with one command (bcp) and referential integrity is effortless. Whereas, with an identity column, you will have to do some manual labour (eg. insert instead of bcp), and a hell of a lot of checking to ensure referential integrity is not lost; your "exact data" requirement will have to be manually enforced. Note the actual time you spend on the task.

    Some of us decided a long time ago, to use carefully chosen logical keys and to never use identity columns. If you need any object independence (administration), it is just not worth the manual labour.

    "Mind the gap"

    Cheers
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  5. #5
    Join Date
    Nov 2004
    Posts
    27
    I am making progess - but now I am hung where the identity columns has skipped identity values from 41 to 5000041.

    I tried the SQL between but it does not seem to work.

    insert ERROR_TYPE_COMP_BY(ERROR_COMP_BY_ID, ERROR_TYPE, syb_identity)
    values (6, "Airline Res", 5000042)
    go

    error message

    Explicit value specified for identity field in table 'ERROR_TYPE_COMP_BY' when 'SET IDENTITY_INSERT' is OFF.

    set identity_insert ERROR_TYPE_COMP_BY on

    error message

    Unable to 'SET IDENTITY_INSERT' for table 'ERROR_TYPE_COMP_BY' because IDENTITY_INSERT or IDENTITY_UPDATE is already ON for the table '<unknown table name>' in database 'Intranet_test'.

    Any further insight would be appreciated.

    Patrick Quinn

    TQ3 Navigant Sybase database admin

  6. #6
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    Lazy programmer looks good, DBA looks bad ...

    First I said:
    Wait until you start to deal with the identity gaps
    Then I said:
    Mind the gaps
    Soon you will be writing complex SQL using temp tables. At some point, you will decide that you need a reliable (unchanging) key, ie. not an identity column; and you will define that real key in the temp table; at that point your SQL will start to work and you will have a reliable result (corrected table); at that point you might decide never to use identity columns for keys (outside a lazy prototyping environment). Unless you are paid well for spending your time developing workarounds for substandard developers.

    I am sure there are respondents who will tell you that identity columns can be managed. Evidently they have not had the benefit of your current experience, and they do not have the need for migrating objects (eg. move table xyz from server1.dbprod to server2.dbdev) with one command, without suffering (referential integrity, index, insert, "but the key in prod is different from the key in dev!") problems.

    Beware/1
    There are websites that offer scripts that purport to handle (correct, eliminate, mutilate) the identity gaps. Weird, for such a manageable facility. They are free, and worth exactly what you pay for them. Be warned, they are absolute crap and they will further damage your table. I have just repaired a customers db after he used these in exactly the scenario you have. He spent 10 mandays working on the problem which was supposed to take 30 minutes, and he too now has a site rule "NO IDENTITY COLUMNS". My solution was one billable day and I rebuilt all tables with identity columns, replacing same with a real (logical, relational, reliable) key (it took him an elapsed month to identify what the real keys should be, this work that was avoided during prototyping had to be done eventually). Unsurprisingly, the db is faster.

    Prevention is infinitely better than cure. But we have to experience the pain first, in order to value not having it.

    If you have the opportunity, you may wish to rebuild the table/s with real keys first, and migrate it second. The effort will be LESS THAN that which you expend in fixing the symptom. Consider how many times you will have to do this again in the future.

    Beware/2
    You are now minding the identity gap problems. There are more problems after this one. I repeat:
    Watch out for unintentionally propagating the foreign key problems.
    The next problem in your current path is consequential Referential Integrity issues (check where the identity column is used as an FK in child tables). That is, assuming you do have Referential Integrity.

    Good luck
    Last edited by DerekA; 07-27-06 at 02:31.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  7. #7
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    powerdude,

    You need to make sure that the "set identity_insert" option is turned off for any other table in the system before you can turn it on for the new one.

    Michael

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •