What is faster - insert into new table, or delete from existing table?

Question

I have a huge DB that I want to scale down, to only look at my area of interest. Initially I wanted to write the records I'm interested in onto a new database and table. However, this is taking for ever, so I'm wondering if it might be faster to delete the records I do not want. I already got a copy of the database, so there would be no problem to delete.

Insert statement:

INSERT INTO NewTable (C1, C2, C3, C4, C5, C6, C7) 
SELECT OldTable1.C1, OldTable1.C2, OldTable1.C3, OldTable1.C4, OldTable1.C5, OldTable1.C6, OldTable1.C7 
FROM OldTable1 
INNER JOIN OldTable2.C5 ON OldTable2.C5 = OldTable1.C5 
WHERE OldTable2.C8 = 70

If I would delete I would delete all data from OldTable1 that did not match the inner join and where statement.

Answer 1

It very much depends on the details of your database.

Factors include:

• Number of records to be copied/retained;
• Whether you're keeping all columns or not; and
• Number of indexes on old and new table (as alluded to by @a_vlad).

If I needed to delete most of the records in a table, I have definitely had times where it was faster to copy the records to another table, TRUNCATE the first table, and then copy the records to be saved back to the first table (with all indexes, constraints, etc.; the other table would usually be stripped down to just the raw data).

That said, if you're keeping notably more records than you're deleting, and if you don't have a lot of indexes on the original table, it's entirely possible that deleting would be faster.

NOTE: If you don't need to keep all columns, then the INSERT method is almost certainly your best bet.