Update the table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update the table

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2010-01-23 : 11:25:37

I have a table with 5 columns. One column contains clustered index on it. The table contains around 300 million records(population data). Now i added new column FNTag to the table. Now i am trying to update the table to fill this FNTag column by making a inner join query.

"Mytable" contains following columns

FirstName
LastName
State
City
Zipcode
Now i add new column "FNTag" to the table. And i am trying to update the table with below query.(here i am making innerjoin with another table Tbl_FirstName(200000 records).

Update t2 Set
t2.FNTag= t1.Ancestry
From MyTable As t2
INNER JOIN (select distinct Name, stuff((select distinct ','+ Ancestry from TBL_FirstName where name = t.name for xml path('')),1,1,'')as Ancestry
from TBL_FirstName t
) As t1 on t2.FirstName = t1.Name

Everything goes fine. But when i start executing above query..after 1 hr my system getting restarted automatically. When i check in event logs it showing some fatal errors. We can't update 300 million records at a time? if so any other process to do this?

developer :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-24 : 09:13:30

can you see how many records the inner stuff query is returning for a typical value of name?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources