How to find duplicate Value in SQL

Identify Duplicate Criteria

The first step is to define your criteria for a duplicate row. Do you need a combination of two columns to be unique together, or are you simply searching for duplicates in a single column? In this example, we are searching for duplicates across two columns in our Users table: username and email.

Write Query to Verify Duplicates Exist

The first query we’re going to write is a simple query to verify whether duplicates do indeed exist in the table. For our example, my query looks like this:

SELECT username, email, COUNT(*)
FROM users
GROUP BY username, email
HAVING COUNT(*) > 1

HAVING is important here because unlike WHEREHAVING filters on aggregate functions.

If any rows are returned, that means we have duplicates. In this example, our results look like this:

USERNAMEEMAILCOUNT
Petepete@example.com2
Jessicajessica@example.com2
Milesmiles@example.com2

List All Rows Containing Duplicates

In the previous step, our query returned a list of duplicates. Now, we want to return the entire record for each duplicate row.

To accomplish this, we’ll need to select the entire table and join that to our duplicate rows. Our query looks like this:

SELECT a.*
FROM users a
JOIN (SELECT username, email, COUNT(*)
FROM users 
GROUP BY username, email
HAVING count(*) > 1 ) b
ON a.username = b.username
AND a.email = b.email
ORDER BY a.email

If you look closely, you’ll see that this query is not so complicated. The initial SELECT simply selects every column in the users table, and then inner joins it with the duplicated data table from our initial query. Because we’re joining the table to itself, it’s necessary to use aliases (here, we’re using a and b) to label the two versions.

Here is what our results look like for this query:

IDUSERNAMEEMAIL
1Petepete@example.com
6Petepete@example.com
12Jessicajessica@example.com
13Jessicajessica@example.com
2Milesmiles@example.com
9Milesmiles@example.com

Because this result set includes all of the row ids, we can use it to help us deduplicate the rows later.


Backup using SQL Query and Task Scheduler

Sage Evolution comes with SQL Express Editions but SQL Server Express editions do not offer a way to schedule either jobs or maintenance plans because the SQL Server Agent component is not included in these editions. Therefore, you have to take a different approach to back up your databases when you use these editions.

This article describes how to use a Transact-SQL script together with Windows Task Scheduler to automate backups of SQL Server Express databases on a scheduled basis.

If you are running MS SQL Standard or Enterprise Editions see Scheduling Backup with SQL Server 2012 Standard  Editions

Step 1

Download the script from the link below

 

backup view

Extract the files to your local root folder C:/ 

Open the Backup2.txt and edit the database instance name

backup2

Save the file name as Backup2.cmd

backup 3

 Step 2

Go to Start…All Programs….System tools…..Task Scheduler

backup 4

backup5

Click on Import Task

Locate Evolution Backups.xml extracted from the downloaded file

backup6

 

Click Open

backup7

 

Click other tabs to configure to your taste.

Don’t forget to drop a comment