20 million records and alter table

Posted on 2001-06-02
Last Modified: 2008-02-01
Hi All,

I have Sql-Server2000 and i have one table which has 20 million records.
Now i wanna do alter table on that i.e. i want to add identity column. when i tried firing alter table query on that the log file increased to 6GB but still was not able to execute that query and so i stopped it and some how restored data back.
Since this is live data i don't wanna take any more risk.
what i thought is say using bcp if i copy the data to text file and then if i do alter table it will work but while restoring data back from text file will it also populate the newly added column with the values like 1,2,3,4........ till 20 million.

So my concern is now on such a large table what is the best way to add a identity column with the values populated.

Expecting a good and reliable help.
Please note that this is live data.


Question by:arpit080399
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 6150193
Have you tried creating the new structure as a different table name and a dts package from the old table into the new one and setting a commit count of 10000 or 20000?
then drop the old and rename the new?
do you have diskspace for 2 of these tables on th same db??

LVL 18

Expert Comment

ID: 6150573
Do you need an identity column?

If so then probably the bcp option is the most risk free - set the block size to something reasonably small.

You could also create the new column and populate it using a trigger - this will slow down updates to the table but is probably the easiest to implement.

Set a query like
set rowcount 0
while 1 = 0
update tbl
set newcol = (select max(newcol) + 1 from tbl)
where newcol is null

You can just set this query to run at quiete times until the whole table is populated.

Another option if your system is set up to allow it.
Create a new table with the identity property.
Rename the old table.
Create a view which unions the two tables.
Set a query going which copies rows between the two tables and deletes from the old.
When it has completed drop the old table, drop the view and rename the new table.

Expert Comment

ID: 6157178

You can use the select into statement to create the new table with identity value as followss in sql 2000 with identity function. As you must be knowing select into is non logged operation so it will not full your transaction log files.

I have given you the sample query.

Select identity(int, 1,1) as empid, emp_name, address1
     into newemp from employee

Hope this works for you.

Good luck


Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now


Author Comment

ID: 6158235
hey mgmanoj


your suggestion seems to be what i am looking for? can you please tell me this query in bit detail.
i.e. say my table A has 20 million record and has columns like a,b,c and i wanna make a table B which should have all the columns of table A and plus identity column.
Will the value in the identity column be autoincrement as it copies one-one row from table A. if no then how do insert it into that identity column.


Accepted Solution

mgmanoj earned 100 total points
ID: 6160286

Yes it is auto increment by 1 as per return in my query. If you see the detailed explanation of identity function you will understand that very well.

This is the new function they introduced from SQL 2000. Previously with 7 it was big problem but as your question said you are using 2000 so as per my knowledge this is the best way to add identity column without loggin in the transaction log & select into has remain always the fastest to copy data from one table to another.


LVL 18

Expert Comment

ID: 6160483
Actually it was introduced with v7 but doesn't affect the suggestion.

Expert Comment

ID: 6833286
This question appears to have been abandoned. All your open questions today are at the bottom.   Your options are:
1.  Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you. You must tell the participants why you wish to do this, and allow for Expert response.
3.  Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4.  Delete the question. Again, you must tell the other participants why you wish to do this.

For special handling needs, please post a zero point question in the link below, include the question QID/link.
Please click this Help Desk link for Member Guidelines, Member Agreement and the Question/Answer process:  Click you Member Profile to view your question history and keep them all current with updates as the collaboration effort continues.

------------>  EXPERTS:
Please leave any comments regarding this question here on closing recommendations if this item remains inactive another three days.
Thank you everyone.
Moderator @ Experts Exchange
P.S.  For year 2000 question, special attention is needed to ensure the first correct response is awarded, since they are not in the comment date order, but rather in Member ID order.



Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question