question

queen avatar image
queen asked

Load the record into new table and delete from old table in batches

I'm looking to execute a insert statement that insert database rows based on time frame in a new table in batches of 10000. After moved to destination table delete the record in source table table .I have problem where is not inserting all the records in destination table and all the records get deleted. Is that any issue with my while loop statement .What could be the issue with my below query


 DECLARE @batchcount INT;
 SET @batchcount =100000
DECLARE @reccount INT;

SELECT @reccount = count(*) from SOURCE_TABLE (nolock)  WHERE   update_dts >= '2022-05-05'
AND update_dts <= '2022-05-10'

INSERT INTO [DESTINATION_TABLE]
           ([num]
           ,[cID]
           ,[status_code]
           ,[price]
           ,[unrounded_price]
           ,[active_price_rule_id]
           ,[AC]
           ,[active_type_code]
           ,[active_ref_num]
           ,[active_comp]
           ,[active_forecasted]
           ,[discount_class_code]
           ,[flag]
           ,[mflag]
           ,[maintflag]
           ,[currency_code]
           ,[publish_code]
           ,[update_dts]
           ,[update_uid]
           ,[fulfillment_group_id]
           ,[msrp]
           ,[min_adv]
           ,[shippable_type_id])
SELECT 
[num]
      ,[cID]
      ,[status_code]
      ,[price]
      ,[unrounded_price]
      ,[active_price_rule_id]
      ,[AC]
      ,[active_type_code]
      ,[active_cost ref_num]
      ,[active_comp]
      ,[active_forecasted]
      ,[discount_class_code]
      ,[flag]
      ,[mflag]
      ,[maintflag]
      ,[currency_code]
      ,[publish_code]
      ,[update_dts]
      ,[update_uid]
      ,[fulfillment_group_id]
      ,[msrp]
      ,[min_adv]
      ,[shippable_type_id]
FROM [dbo].SOURCE_TABLE 
WHERE  update_dts >= '2022-05-10'
AND update_dts <= '2022-05-11'
 
WHILE EXISTS (select TOP 1 hist_id FROM [SOURCE_TABLE ] (NOLOCK) where   update_dts >= '2022-05-10'
AND update_dts <= '2022-05-11')
 
BEGIN
 
-- delete every 10K
SET ROWCOUNT 10000
 
DELETE [SOURCE_TABLE]
WHERE  update_dts >= '2022-05-10'
AND update_dts <= '2022-05-11'
 
 
Print CAST(@@ROWCOUNT as nvarchar(20))+' Records deleted'
 
SET ROWCOUNT 0 
sql-server-2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Jeff Moden avatar image
Jeff Moden answered

Yes... there's an issue with your code. You're relying on some sort of natural order to do the deletes. There's no such thing as a "RELIABLE" natural order in SQL. What is the primary key of the two tables?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.