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

Question

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246686 More actions · Answer 1

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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

rohitkocharda Say Hey Kid Points: 687 More actions · Answer 2

let me explain my issue again:

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

Phil Parkin SSC Guru Points: 246686 More actions · Answer 3

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.