question

5QL53rv3r avatar image
5QL53rv3r asked

In SSIS can you loop through a file and delete 1000 rows @ a time?

I have an SSIS package and I am trying to delete the rows from a table that appear in a raw file, is this possible? The SSIS package identifies the required rows in a read only copy of the LIVE database, writes them to a raw file, inserts them into an archive database. The delete from LIVE is the next step. Ideally I would like the deletes to have as minimum an impact as possible, hence the 1000 rows. M
ssisloopforeach
10 |1200

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

WilliamD avatar image
WilliamD answered
I'd go for either a CTE (SQL 2005 and above) or MERGE (SQL 2008 and above). Merge should be more efficient, as it will do a scan on both tables, merge join them and delete what is necessary. The CTE would probably perform well, but the execution on my test showed it to do a loop join, which would be more costly - this could be caused through the lack of statistics on my example table variable. ***Both of my solutions join to the archive table though, ensuring that you are only deleting rows that really are in the archive.*** Example 1 - CTE to find the rows to delete: ---------------------------------------- /* Create Test Tables */ DECLARE @LiveTable AS TABLE (Col1 int) DECLARE @ArchiveTable AS TABLE (Col1 int) /* Fill test tables */ INSERT INTO @LiveTable (Col1) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 INSERT INTO @ArchiveTable (Col1) SELECT 1 UNION ALL SELECT 2 /* Declare maximum number of rows to be processed */ DECLARE @BatchSize int = 1000 /* Delete the data that exists in Archive and LiveTable */ ; WITH toDelete AS (SELECT TOP (@BatchSize) L.* FROM @Livetable L WHERE EXISTS ( SELECT * FROM @ArchiveTable A WHERE L.Col1 = A.Col1 )) DELETE FROM toDelete ; /* Display the rest of @LiveTable*/ SELECT * FROM @LiveTable Example 2 - MERGE command (SQL 2008 and newer): ---------------------------------------- /* Create Test Tables */ DECLARE @LiveTable AS TABLE (Col1 int) DECLARE @ArchiveTable AS TABLE (Col1 int) /* Fill test tables */ INSERT INTO @LiveTable (Col1) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 INSERT INTO @ArchiveTable (Col1) SELECT 1 UNION ALL SELECT 2 /* Declare maximum number of rows to be processed */ DECLARE @BatchSize int = 1000 /* Delete the data that exists in Archive and LiveTable using MERGE */ ; MERGE @LiveTable tgt USING (SELECT TOP (@BatchSize) * FROM @ArchiveTable AT) src ON tgt.Col1 = src.Col1 WHEN MATCHED THEN DELETE ; /* Display the rest of @LiveTable*/ SELECT * FROM @LiveTable
6 comments
10 |1200

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

Cyborg avatar image Cyborg commented ·
+1 Love to see Merge
0 Likes 0 ·
5QL53rv3r avatar image 5QL53rv3r commented ·
Thanks - not expecting a whole solution - so thanks again & +1!
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
And that +1 from Cyborg put you ahead of me in the karma-stakes. And here's a +1 from me to go with it, just to show I'm not bitter.
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
Damn, never noticed that. I knew I was coming close..... thanks for that :)
0 Likes 0 ·
Cyborg avatar image Cyborg commented ·
ASKSSC Monitoring? :)
0 Likes 0 ·
Show more comments
Fatherjack avatar image
Fatherjack answered
you could use SET ROWCOUNT 1000 DELETE FROM SET ROWCOUNT 0 but this might not delete the correct rows without some criteria or a join to the exported rows.
4 comments
10 |1200

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

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
+1 from me anyway, surprising how many people ignore `set rowcount`... Closing in on 5 digits now sir!
1 Like 1 ·
WilliamD avatar image WilliamD commented ·
A cheeky way of making 1000 rows happen, but as you say, no join to the exported rows so you may be deleting rows you shouldn't
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
heh! was hoping the OP could add that bit as needed
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
@Matt - yeah, its creeping along. Wonder if I'll make it before SQLBits?!
0 Likes 0 ·

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.