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

Expert Comment

Comment Utility
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

Comment Utility
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

Comment Utility

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


How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails


Author Comment

Comment Utility
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
Comment Utility

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

Comment Utility
Actually it was introduced with v7 but doesn't affect the suggestion.

Expert Comment

Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now