Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sean_B
Posting Yak Master
111 Posts |
 Posted - 2009-08-10 : 08:02:58
|
| Hi,I've a very large table (100 Gb) that I'm changing, I'm adding a new column that is a foreign key and is going to be part of a unique index. I'm retaining the Primary key of the table which is simply an identity column [TradeID] [int] IDENTITY(1,1) NOT NULL,I've doing the change by selecting the original data into a new table that includes the new column, I then reindex the new table, and I made the tradeid column back into a primary key, however there appears to be no way to turn it into an identity column.Should I have created the new table first and done an insert into statment having made the trade column an identity column ? Is there no way to change a column into an identity column ? I tried adding the new column to the existing table and running an update, split into smaller transactions to prevent the log file building up, but this was going to take too long.Sean |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-10 : 14:27:49
|
| instead of select into, create the new table with the identity column. then set identity_insert on to populate.for similar sized table i did this:BCP data out of old table in chunks (based on PK), usually a few hundred thousand records per file, then BULK INSERT data back in to new table, keeping identity values.pretty fast and no impact to tran log. |
 |
|
|
Sean_B
Posting Yak Master
111 Posts |
Posted - 2009-08-11 : 06:40:49
|
| Thanks I'll give this a try, I'd got round to select into creating a new identity column as part of the select into, took 2 hours to do a 100 Gb table.Sean |
|
|
|
|
|
|
|
|
making an existing column an identity column