Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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