• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

20 million records and alter table

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.

Thanks.


0
arpit080399
Asked:
arpit080399
1 Solution
 
acampomaCommented:
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??

0
 
nigelrivettCommented:
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.
0
 
mgmanojCommented:
Hi

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

Manoj

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
arpit080399Author Commented:
hey mgmanoj

DONT WORRY I WILL ACCEPT YOUR COMMENT AS ANSWER LATER.

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.

0
 
mgmanojCommented:
Arpit

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.

Thanks

Manoj
0
 
nigelrivettCommented:
Actually it was introduced with v7 but doesn't affect the suggestion.
0
 
MoondancerCommented:
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.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
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.
http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp


PLEASE DO NOT AWARD THE POINTS TO ME.  
 
------------>  EXPERTS:
 
Please leave any comments regarding this question here on closing recommendations if this item remains inactive another three days.
 
Thank you everyone.
 
Moondancer
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.

--------------------------------------------------------------------------------------------------------------------------
http://www.experts-exchange.com/jsp/qShow.jsp?qid=11672199
http://www.experts-exchange.com/jsp/qShow.jsp?qid=11777958
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20016354
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20128960
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20147378
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20156655
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20265599
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20271267

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now