March 2, 2006 at 7:54 am
Can someone enhance this script (Syntactically) to perform the following:
SPECS:
- 20 million row table contains 19 million bogus rows
- Need to delete the 19 million bogus rows
- Need a script to delete 100 bogus rows at a time based on the commit_frequency < 101
--***************************************************
declare @commit_frequency int
set @commit_frequency = 1
DECLARE RunIDCsr CURSOR FOR
SELECT RunID FROM table_name WHERE predicate(s) here
OPEN RunIDCsr
FETCH NEXT FROM RunIDCsr --into @LWClientID
WHILE @@fetch_status = 0
BEGIN
DELETE from table_name where current of RunIDCsr
-- get next row
FETCH NEXT FROM RunIDCsr --into @LWClientID
END
-- Clean up cursor
CLOSE RunIDCsr
DEALLOCATE RunIDCsr
select top 100 * from bogus_table where bogus criteria is met
while @commit_frequency < 101
begin tran DeleteRows
delete from table where col_1 = 'a' and col_2 = 'b'
commit tran DeleteRows
March 2, 2006 at 9:05 am
I would try a little different approach
I would create a temp table, then copy the good 1 million rows to it. Using dts, or BCP (Using the commit 1000 rows at a time option)
Then drop the old table, and rename temp table to the name of the old table.
Make sure you script all indexes, foreign keys, triggers, and constraints prior to dropping the old table.
It will take you much less time.
March 2, 2006 at 9:05 am
If you want to delete records by 100 then you can use::
Delete t from table_name t join
(select top 100 * from table_name ) t1
on t.col1=t1.col1
where your condition
Regards
Amit Gupta
March 2, 2006 at 11:59 am
Thx for everyone's help across threads here. Here's a solution which loops thru 135 million rows, deleteing 10,000 rows then commiting:
DECLARE @x INT, @y INT
SELECT @y = 1
WHILE
@Y > 0
BEGIN
SET @x = 1
SET ROWCOUNT 10000
SELECT @y = COUNT(*) FROM table1
WHERE COL_1 in ('E02453', 'E02256', 'E00861', 'E01388')
AND COL_2 is Null
AND COL_3 = '11/30/2005'
WHILE @x > 0
BEGIN
BEGIN TRAN
DELETE table1
WHERE COL_1in ('E02453', 'E02256', 'E00861', 'E01388')
AND COL_2 is Null
AND COL_3 = '11/30/2005'
SET @x = @@rowcount
COMMIT TRAN
END
END
March 3, 2006 at 3:44 pm
Why not store the 1million correct records into some table and truncate the ori table and insert the 1million back??
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply