locked
How to update parent table identity column values in child table RRS feed

  • Question

  • Hello,

    I have parent and child tables and already populated with data. We have identity column in parent table and we are using this column values in child table. 

    We found an issue that parent table identity values are not sequence because we deleted some records and insert another records without reseeding the identity.

    Now we have reinserted all the data into parent table and identity values are now in sequence. But we have child table with huge records with old parent id values and we need to update those values with new one.

    Can someone provide any solution on this?


    Anil

    Wednesday, March 4, 2020 9:18 AM

Answers

  • How are parent and child table currently linked (PK/FK).

    And why do you need the identity column with values in sequence; it's just an ID?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, March 4, 2020 12:41 PM
    Answerer
  • Identity fields are not sequential.  There are expected gaps.  This is perfectly normal and expected.  You should not reseed the identity just because you deleted some records.

    If you have already reinserted the data with new PKs into the parent table, you have lost all connection to the children.  There is no way to relink the child records to the old parent.

    Wednesday, March 4, 2020 12:42 PM
    Answerer
  • Did you save original IDs somewhere? E.g. do you still have some link between parent and child records you can use to re-link? 

    May be you saved your original table with old PK? If yes, you would need to first join your old original with the new table based on row_number() over (order by PK) and this way you'll be able to have old and new PK to re-link your child table.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, March 4, 2020 2:10 PM
    Answerer
  • I have parent and child tables and already populated with data. We have identity column in parent table and we are using this column values in child table. 

    We found an issue that parent table identity values are not sequence because we deleted some records and insert another records without reseeding the identity.

    IDENTITY and contiquos numbers are two concepts in conflict. IDENTITY is designed to be able to produce gaps. If you have a requirement that IDs should be gapless, don't use IDENTITY.

    Now we have reinserted all the data into parent table and identity values are now in sequence. But we have child table with huge records with old parent id values and we need to update those values with new one.

    Can someone provide any solution on this?

    If you did not save a mapping table with the old and new IDs, you will need to restore a backup a start over.

    Had you don't been using an IDENTITY, you could have updated the ID value, and had you had a cascading foreign key in place, the child tables would also have been updated.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, March 4, 2020 10:40 PM

All replies

  • hello friend

    Generate script of the data only from the sql server for that child table .

    pest in query editor ,Then run .

    1-But some rows are not inserted throws errors those id not match to parent table if you add the foreign key .

    or

    drop the foreign key ,then insert no error

     

    Wednesday, March 4, 2020 10:59 AM
  • Can you post sample data along with desired result?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, March 4, 2020 11:31 AM
  • How are parent and child table currently linked (PK/FK).

    And why do you need the identity column with values in sequence; it's just an ID?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, March 4, 2020 12:41 PM
    Answerer
  • Identity fields are not sequential.  There are expected gaps.  This is perfectly normal and expected.  You should not reseed the identity just because you deleted some records.

    If you have already reinserted the data with new PKs into the parent table, you have lost all connection to the children.  There is no way to relink the child records to the old parent.

    Wednesday, March 4, 2020 12:42 PM
    Answerer
  • Did you save original IDs somewhere? E.g. do you still have some link between parent and child records you can use to re-link? 

    May be you saved your original table with old PK? If yes, you would need to first join your old original with the new table based on row_number() over (order by PK) and this way you'll be able to have old and new PK to re-link your child table.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, March 4, 2020 2:10 PM
    Answerer
  • I have parent and child tables and already populated with data. We have identity column in parent table and we are using this column values in child table. 

    We found an issue that parent table identity values are not sequence because we deleted some records and insert another records without reseeding the identity.

    IDENTITY and contiquos numbers are two concepts in conflict. IDENTITY is designed to be able to produce gaps. If you have a requirement that IDs should be gapless, don't use IDENTITY.

    Now we have reinserted all the data into parent table and identity values are now in sequence. But we have child table with huge records with old parent id values and we need to update those values with new one.

    Can someone provide any solution on this?

    If you did not save a mapping table with the old and new IDs, you will need to restore a backup a start over.

    Had you don't been using an IDENTITY, you could have updated the ID value, and had you had a cascading foreign key in place, the child tables would also have been updated.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, March 4, 2020 10:40 PM