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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-10-25 : 18:28:29
|
Lou writes "I have a large update to do to a table. The total number of rows are 6 million. What I would like to do is do this update in groups of maybe 10,000 rows at a time.
Below is the script one of the developer came up with. But it doesn't seem to work correctly. Can you help me modify this script to do it in chucks of 10,000.
if exists (select * from sysobjects where id = object_id(N'[dbo].[pr_update_accident]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[pr_update_accident]
GO
CREATE PROCEDURE [pr_update_accident] AS
SELECT GETDATE() ' check bad dates'
BEGIN TRANSACTION
update x_table_site_part
set due_date = '01/01/1753'
where isdate(due_date)= 0
COMMIT TRANSACTIONS
SELECT GETDATE() ' BEGIN UPDATE'
BEGIN
BEGIN TRANSACTION
SET ROWCOUNT 10000
update table_site_part
set x_premium_amount = b.premium_amount,
x_last_pay_rec = b.last_payment_amount,
x_symbol = b.symbol_translation,
x_pref_bill_day = b.perferred_billing_day,
x_freeze_flag = b.freeze_flag,
x_due_date = convert(datetime, b.due_date),
x_acct_dt = convert(datetime,accounting_date),
install_date = convert(datetime,issue_date),
instance_name = form_number,
x_policyadmin = 'PICS',
x_original_mode = b.orig_mode_of_payment,
x_original_method =b.orig_method_of_payment
from table_site_part a, x_table_site_part b
where serial_no = policy_number
IF @@ROWCOUNT = 10000
COMMIT TRANS
SET ROWCOUNT 0
END
SELECT GETDATE() ' ACC UPDATE DONE'
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
Thanks
Lou
" |
|
|
|
|
|
|
|
Large update