Delete from table quickly

Question

Delete from table quickly

ChrisStar

Old Hand

Points: 393
More actions
August 19, 2009 at 9:43 am

#225150

I am trying to run a db with a 2003 Access front end and 2008 SQL Server backend using ODBC. I have a Leads table, a CleanLeads table and a Final table. Leads holds all initial contact information. Final will hold records that have been "finalized" and shouldn't be used again. CleanLeads holds the Leads records that haven't been "finalized" and these are later exported out to another system to be used. Currently I am just using 3 statements in VBA to create my CleanLeads table. I'm sure there is a faster way to do this, but I'm not sure how. Can someone help point me in the right direction? Thanks!!! Chris
DoCmd.RunSQL "DELETE CleanLeads.* FROM CleanLeads;"
DoCmd.RunSQL "INSERT INTO CleanLeads SELECT Leads.* FROM Leads;"
DoCmd.RunSQL "DELETE CleanLeads.* FROM CleanLeads INNER JOIN Final ON CleanLeads.AUTOID = Final.AUTOID

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

If you don't have any Foreign Keys defined you could do "Truncate Table CleanLeads" as the first statement. That is usually faster than a delete when you want to "empty" a table.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ChrisStar Old Hand Points: 393 More actions · Answer 2

Thanks, Jack!

I'm going to make the change to my statement and give it a go.

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 3

Chris,

If all 3 tables are in the back-end then you could create a stored procedure & create a pass-thru query to execute that from Access, that would be much faster. The sp might look like this:

CREATE PROC dbo.prcMove_Leads

AS

DELETE CleanLeads

INSERT CleanLeads

SELECT *

FROM Leads

DELETE

CleanLeads

FROM

CleanLeads

INNER JOIN Final

ON CleanLeads.AUTOID = Final.AUTOID

GO

which should work as-is as long as AUTOID is not an indentity column in CleanLeads.

However, I have to ask why there are 3 separate tables - from what you describe maybe you could have a single table with one extra column for the status = Leads, Clean, or Final - in which case "moving" a status is a simple update query.