Deleting Duplicates 2gb issue

[mahmed1]

Hi All

I have been given some sql code where it selects all unique values from a table and then puts into a temporary table but its now giving an error saying its not allowing me to do it because it will go over 2gb

Does anyone have any VBA or SQL code that deletes all duplicate rows from a table - i dont want to get all unique values but do a delete like excel has remove duplicates please

 

[Micron]

Use the query wizard to create a Find Duplicates query. You can then use that query as the basis for a delete query. You probably should try this on a test copy of your db first.
How many records are we talking here? It's uncommon to exceed the limit because of one table, unless perhaps it contains attachments in attachment fields.

 

[jackd]

I agree with micron in that it's rare to see that limit being exceeded with 1 table.

How about posting the SQL you were given?
And the design of the table where the data resides.

You could possibly put the selected data into a table in a different database.... just a thought if current database is close to the 2gb limit.

 

[mahmed1]

Thank you guys once again

Ive been given this db that has been running for a number of years. It runs daily and appends daily snapshots of agent info

There must be stupid amount of duplicate rows (well when i do it in powert query almost 2million records get deleted i think) do the design must have been set up wrong

So I thought id first use the design sql to first get duplicates and then delete it but rather than creating a temp table with unique data which causes the 2gb limit issue, i wanted to just delete the duplicate data instead in the table

Do you guys have Any code that deletes duplicate rows

My table is called tbl_agent_info

 

[jackd]

First thing I'd do is backup the current database- you never know what could happen!!!!!!!!
Don't experiment with your only or live copy of the database.
Make a copy and use that.

As micron said there is a Find Duplicates query wizard. Use that to identify the size of the issue.
Post the SQL so we can see the logic involved.

What field or combination of fields make a duplicate a duplicate?

 

[mahmed1]

Hi - i dont have access to the data at the moment but will post when i do but a duplicate is looking at the whole row of data (there are multiple fields like 25 fields)

The idea behind this table was to apend all agent info and store daily

Do you have any code to hand to remove it whilst i get the sql that was given to me

Im pretty sure its using Microns method where i create a unique table using the wizard

Then i delete everything from my main table
Then i insert into this table

 

[jackd]

see Delete duplicate records with a query

 

[Micron]

Im pretty sure its using Microns method where i create a unique table using the wizard

I never said to create another table.
IIRC there is a limit on how many fields the wizard can handle for that type of query, but I seem to recall that you can add more in design view when the wizard is finished. If every field is exactly the same in the records you think are dupes, then it shouldn't be too hard. However if just one out of 25 fields contains a different value than any other, it technically isn't a duplicate. In that case, you'd have to decide how many fields constitute a duplicate.

 

[james_lankford]

create a new database
from the new database do a link to external data and link to the table in the big database -- LINK NOT IMPORT
then run the sql to create the temp table in the new database

 

[Joe4]

If this database has been used for years, and lots of record deletions happen, it may have database "bloat", since deleting records does NOT reduce the size of a database until you Compact & Repair.
Have you tried running a Compact & Repair and see if that reduces the size of the database? It may reduce it significantly.