Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

20 million records and alter table

Posted on 2001-06-02
7
Medium Priority
?
432 Views
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.

Thanks.


0
Comment
Question by:arpit080399
[X]
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
7 Comments
 
LVL 6

Expert Comment

by:acampoma
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??

0
 
LVL 18

Expert Comment

by:nigelrivett
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.
0
 
LVL 3

Expert Comment

by:mgmanoj
ID: 6157178
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:arpit080399
ID: 6158235
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
 
LVL 3

Accepted Solution

by:
mgmanoj earned 400 total points
ID: 6160286
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
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6160483
Actually it was introduced with v7 but doesn't affect the suggestion.
0
 
LVL 1

Expert Comment

by:Moondancer
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.
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

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.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

618 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