CODEWITHSUNDEEP
sql-servert-sql
Meysam Tolouee
Meysam Tolouee

Reputation: 579

How Merge Duplicate Data using MergeTable?

There are duplicate record in my table. I wrote a query to find them. The result is like this:

+-----------+-------------+-------------+
|    Row    | NationalNo  |  Client ID  |
+-----------+-------------+-------------+
|    1      |     10003   |     34      |
+-----------+-------------+-------------+
|    2      |     10003   |     75      |
+-----------+-------------+-------------+
|    1      |     20023   |     23      |
+-----------+-------------+-------------+
|    2      |     20023   |     55      |
+-----------+-------------+-------------+
|    3      |     20023   |     12      |
+-----------+-------------+-------------+

The above result means we have one client with National-No of 10003 whom inserted twice and another client with National-No of 20023 whom inserted 3 time in Client table. But I am not going to delete the extra. I want to keep the first record active and the rest will be inactive. The Task is to save this actions as History IN MergeTable. MergeTable has 3 Columns: ClientIDA, ClientIDB, Date I want to Consider the records with Row of 1 As ClientIDA and rest of them As ClientIDB. So the output needed to insert into MergeTable is:

+-----------+-----------+-------------+
| ClientIDA | ClientIDB |     Date    |
+-----------+-----------+-------------+
|    34     |     75    |  2014-06-10 |
+-----------+-----------+-------------+
|    23     |     55    |  2014-06-10 |
+-----------+-----------+-------------+
|    23     |     12    |  2014-06-10 |
+-----------+-----------+-------------+

Upvotes: 0

Views: 31

Answers (1)

Darka
Darka

Reputation: 2768

Here is example how u can do.

You split your table into two (data which you insert and data which will not)

And then you just join this two tables.

DECLARE @duplicates TABLE (Row INT, NationalNo INT, ClientID INT)


INSERT INTO @duplicates (Row, NationalNo, ClientID) SELECT 1, 10003, 34
INSERT INTO @duplicates (Row, NationalNo, ClientID) SELECT 2, 10003, 75
INSERT INTO @duplicates (Row, NationalNo, ClientID) SELECT 1, 20023, 23
INSERT INTO @duplicates (Row, NationalNo, ClientID) SELECT 2, 20023, 55
INSERT INTO @duplicates (Row, NationalNo, ClientID) SELECT 3, 20023, 12



;WITH ClientIDA AS (
    SELECT Row, NationalNo, ClientID 
    FROM @duplicates
    WHERE Row = 1
), ClientIDB AS (
    SELECT Row, NationalNo, ClientID 
    FROM @duplicates
    WHERE Row != 1
)
SELECT A.ClientID AS ClientIDA, B.ClientID AS ClientIDB, GETDATE() AS DATE
FROM ClientIDB AS B
INNER JOIN ClientIDA AS A
    ON A.NationalNo = B.NationalNo

Upvotes: 1

Related Questions