how can i do the delete statement without having to re-index?

Question

how can i do the delete statement without having to re-index?

sqlstart wannabe

Old Hand

Points: 365
More actions
August 30, 2010 at 7:29 am

#227046

Hi,
i am pulling data from another source and keeping it in a temproray table and index the temp table.
then i want to delete the intersection of the main table and the temp table.
After that i'll insert the temp table data into main table.
Now, i am using an index in the temp and main tbl to help find the intersection of the temp and main table. But this is in a delete statement, how can i do the delete statement without having to re-index?
Thanks,
Sqlstar
Thanks,
sqlstart

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

beck.patrick Ten Centuries Points: 1167 More actions · Answer 1

Hi!

I'm not sure if i understand it right, but you have indexed the columns you used to join the two tables to find the intersection.

You can use this join also for delete in a statement like this:

delete from m

from [main_table] m

inner join [temp_table] t

on m.[intersection_col] = t.[intersection_col]

This should delete all columns from the main table with would be retrieved by a "INNER JOIN"-Select-Statement.

Nevyn SSChampion Points: 14210 More actions · Answer 2

Is there a reason you are deleting and inserting instead of updating?

drew.allen SSC Guru Points: 76985 More actions · Answer 3

I think he's inserting into a temp table, then deleting from the temp table, and then inserting the remaining records into the production table.

If that is the case, you might want to use the EXCEPT clause. You could use it as part of the statement inserting the data, so that the temp table only has the data that you need (in which case you may not even need a temp table) or you can use it as part of the statement inserting the data into the production table so that you don't have to delete the data from the temp table (if you need all of the records for some other reason). This all assumes that you match the temp data and production data for the EXCEPT clause to work properly.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Nevyn SSChampion Points: 14210 More actions · Answer 4

drew.allen (8/30/2010)
I think he's inserting into a temp table, then deleting from the temp table, and then inserting the remaining records into the production table.
If that is the case, you might want to use the EXCEPT clause. You could use it as part of the statement inserting the data, so that the temp table only has the data that you need (in which case you may not even need a temp table) or you can use it as part of the statement inserting the data into the production table so that you don't have to delete the data from the temp table (if you need all of the records for some other reason). This all assumes that you match the temp data and production data for the EXCEPT clause to work properly.
Drew

Oh, ok. That makes a bit more sense.

But as you point out, it can still be done without a delete. Just do an insert-select that ignores the intersecting records (using except or an outer join checking for the null records).