UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Duplicate Records Issue, Any Versions    
 
   
ordnance1
post Jan 21 2018, 07:32 PM
Post#1



Posts: 515
Joined: 7-May 11



I inherited this table with just over 2,500,000 rows, and no primary key. There are about 7,200 with duplicate TimeStamps (although the remaining data in the rows is not necessarily duplicated). I have been totally unsuccessful at clearing out the duplicate row.

In all my roaming around the internet I determined that I need to get the table in TimeStamp order before a can create something to delete the duplicate rows.

I can run a query that that returns the table sorted by TimeStamp, but I have been unsuccessful in copying the sorted data into a new table. I have tried so many different examples that I have become totally lost and frustrated. It is my desire to get rid of the duplicate TimeStamps so I can add a primary key to the table.

Any help will be greatly appreciated

Attached File  Untitled_picture.png ( 61.12K )Number of downloads: 0


Attached File  Untitled_picture.png ( 61.12K )Number of downloads: 0
Go to the top of the page
 
MadPiet
post Jan 22 2018, 12:36 AM
Post#2



Posts: 2,412
Joined: 27-February 09



CREATE TABLE scripts (right-click the table, select SCRIPT TABLE AS > CREATE > To Clipboard, then paste the SQL in here.
Then give us some sample data (insert statements)

You can do this by using a CTE with ROW_NUMBER() to identify the duplicate records. Then delete them (ROW_NUMBER() will number the duplicates from 1 to N... so you just delete all the records where the ROW_NUMBER result is >1.

Here's a quick example.

CODE
use tempdb;
GO

CREATE TABLE Dummy(
    BadID INT
);
GO

INSERT INTO Dummy(BadID) VALUES (1),(1),(2),(3),(4),(4),(5),(5),(5),(6);

;WITH cteDupes (ID, rn)
AS
-- define the query (CTE)
(    SELECT BadID
    , ROW_NUMBER() OVER (PARTITION BY BadID ORDER BY BadID) rn
    FROM Dummy
)
-- use the CTE to return some records
    DELETE
    FROM cteDupes
    WHERE rn > 1;


Since you're using a DELETE query, I would change the DELETE... to a SELECT query so you can be sure you're returning the records you intend to delete before actually doing it.

Here's an example of Common Table Expressions from Microsoft: https://technet.microsoft.com/en-us/library...v=SQL.105).aspx
This post has been edited by MadPiet: Jan 22 2018, 12:37 AM
Go to the top of the page
 
ordnance1
post Jan 22 2018, 02:20 AM
Post#3



Posts: 515
Joined: 7-May 11



Problem Solved.

Pulled the data into MS Access did my cleanup, so simple in Access, then exported back to SQL Server.
Go to the top of the page
 
MadPiet
post Jan 22 2018, 02:47 AM
Post#4



Posts: 2,412
Joined: 27-February 09



Hey, whatever works for you. Personally, I hate querying in Access. But like I said, to each his own.
Care to share your code?
Go to the top of the page
 
nvogel
post Jan 22 2018, 08:20 AM
Post#5



Posts: 842
Joined: 26-January 14
From: London, UK


May be worth mentioning that TIMESTAMP is a reserved word in SQL Server and the (deprecated) name of a datatype. It's not a good idea to use TIMESTAMP as a column name.

In the long term you can probably be more productive if you try to improve your Transact SQL skills. The SQL Server solution to your problem would have been MUCH easier and less error-prone than copying the data out to Access and back again.


Go to the top of the page
 
GroverParkGeorge
post Jan 22 2018, 09:35 AM
Post#6


UA Admin
Posts: 32,393
Joined: 20-June 02
From: Newcastle, WA


Just to complete the thought. As noted, the deprecated name Timestamp was replaced by RowVersion, although the last time I looked, SQL Server, maddeningly, STILL calls it timestamp when you create it via the GUI designer.
Attached File  UATimeStamp.jpg ( 30.91K )Number of downloads: 0

--------------------
Go to the top of the page
 
nvogel
post Jan 22 2018, 12:09 PM
Post#7



Posts: 842
Joined: 26-January 14
From: London, UK


Quite true George. In the example given in this thread the column called TimeStamp is actually a DATETIME type, not a TIMESTAMP (ROWVERSION). So the name is particularly misleading.
Go to the top of the page
 
GroverParkGeorge
post Jan 22 2018, 01:05 PM
Post#8


UA Admin
Posts: 32,393
Joined: 20-June 02
From: Newcastle, WA


Consistency is not a strong point at MS, is it?

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd April 2018 - 10:05 AM