Get X amount of records and truncate table then re-insert

Question

Hi

I have a single table with 2 million rows. I need only a thousand of them and i can get them easily by doing a where clause such as

SELECT FROM MYTABLE  
WHERE        (IsBroken = 0)

I tried deleting the records using where IsBroken = 1 and 'Delete from' but this was writing to the Transaction log and that got full quickly.

Is there a way that i could get all the required records store them temporarily and then truncate the table and re-insert the temporary records i require?

Answer 1

You can delete them in batches to prevent the transaction log from getting too full.

DECLARE @rowcnt INT = 1;

WHILE @rowcnt > 0
BEGIN
    DELETE TOP (1000) FROM MYTABLE 
    WHERE        (IsBroken = 0)

    SET @rowcnt = @@ROWCOUNT;
END

Answer 2

Try this:

SELECT *
INTO MYTABLE_Temp
FROM MYTABLE
WHERE IsBroken = 0;

TRUNCATE TABLE MYTABLE;

INSERT INTO MYTABLE
SELECT * FROM MYTABLE_Temp;

DROP TABLE MYTABLE_Temp;

SELECT * FROM MYTABLE;

Answer 3

Hi MrEdge-3552,
In addition, you can use truncate table statement as GuoxiongYuan mentioned, because TRUNCATE TABLE is faster and uses fewer system and transaction log resources.
But note that you cannot use TRUNCATE TABLE on tables that:

• Are referenced by a FOREIGN KEY constraint. You can truncate a table that has a foreign key that references itself.
• Participate in an indexed view.
• Are published by using transactional replication or merge replication.
• Are system-versioned temporal.
• Are referenced by an EDGE constraint.

For tables with one or more of these characteristics, please use the DELETE statement instead.

Please refer to this doc for more details.

Best Regards,
Amelia

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.