Setting up the identity key after creating a new table with data

• rohitkocharda

  Say Hey Kid

  Points: 687

  More actions

  April 12, 2020 at 4:00 pm

  #3742294

  Hi everyone,

  I have a giant table 'A' with an identity key and also a foreign key that references the identity key in the same table. My goal is load some data in this table from some other table 'B' for some data analysis. Approach i took was remove the identity key form tbale, truncate the table and load the data in table A from table B. After the data  is loaded, I tried to set up the identity key for the original column but sql won't let me do that. Is there a way to load the data to table 'B' and also preserve the value of identitykey?

   

  Thanks,

  Rohit

• Phil Parkin

  SSC Guru

  Points: 247180

  More actions

  April 12, 2020 at 4:06 pm

  #3742295

  If I understand what you are asking, SET IDENTITY_INSERT may be what you are looking for.

  ---

• rohitkocharda

  Say Hey Kid

  Points: 687

  More actions

  April 12, 2020 at 9:13 pm

  #3742315

  let me explain my issue again:

  1. table A has a key set as primary key and identity set .
  2. I truncated the table
  3. I removed the identity key and primary
  4. I populated the table with almost 15 million rows
  5. Now, I tried setting th column back to primary key and identity
  6. got timeout expiration error
  7. changed the expiration to the max
  8. tried setting the identity key again and its been more than 2 hrs and it is still doing this
  9. I did this POC for a  small table and this is possible
  10. Is there a better way to do this?

   

• Phil Parkin

  SSC Guru

  Points: 247180

  More actions

  April 13, 2020 at 2:48 pm

  #3742422

  Please explain your thinking behind removing and adding the IDENTITY column. Why not leave the IDENTITY column there during data load?

  The PK is a separate matter, and there may be value in dropping that before the load. Thorough testing will provide the answer to that.

  ---