CODEWITHSUNDEEP
sql-serverpowershell
BobbyS
BobbyS

Reputation: 49

Deleting database records in batches

I'm looking to execute a delete statement that deletes database rows in a table in batches of 1000. The command is being called from a powershell script. The SQL command is below ...

USE AIS
GO
DECLARE

   @rows INT = 1,
   @batch_size INT = 1000,
      @duration_in_days INT = -4 

   WHILE @rows > 0
   BEGIN
   --First delete all child rows to avoid FK issues
DELETE TOP (@batch_size)
FROM dbo.ais_audit
WHERE create_time < dateadd(day, @duration_in_days, getdate())
   AND parent_audit_id IS NOT NULL;

   --Now delete any parent rows
   DELETE TOP (@batch_size)
FROM dbo.ais_audit
WHERE create_time < dateadd(day, @duration_in_days, getdate())
   AND parent_audit_id IS NULL;

SELECT @rows = @@ROWCOUNT;    
   SELECT @rows AS 'DELETED ROW COUNT';
WAITFOR DELAY '00:00:01';

END "@

The command I'm using to execute this in powershell is

Invoke-Sqlcmd -ServerInstance $Server -Database $Database -AccessToken $tok.Token -Query $command

Upon running the above, the first 1000 records get deleted, but then it fails with the following error

" Invoke-Sqlcmd : The DELETE statement conflicted with the SAME TABLE REFERENCE constraint 
"FK_AIS_AUDIT". The conflict occurred in database "AIS", table "dbo.ais_audit", column 
'parent_audit_id'.
The statement has been terminated. "

Running the SQL statement from SSMS works fine, so this is only an issue in Powershell it seems.

Upvotes: 0

Views: 1082

Answers (1)

Deirdre O&#39;Leary
Deirdre O&#39;Leary

Reputation: 450

Based on your T-SQL, you have no guarantee that the batch of parent rows that you are trying to delete are in any way related to the batch of child rows that you have just deleted. To ensure that you don't have a foreign key conflict, you will need to either:

a) delete all batches of child rows before moving onto deleting the parent rows, or

b) record the list of parent_audit_ids for the batch of child rows that you are deleting & then use this list to delete the associated parent rows.

A is the easier option, but B will allow you to delete children & parent rows in associated batches. Which you choose is dependent on your requirements.

Upvotes: 1

Related Questions