This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script to copy a unique record from one table to another.
You can perform this activity using ROW_NUMBER() and PARTITION BY clause.
This partition clause identified set of duplicate records and row_number assign an ID for this set.
Below is a full demonstration:
Let’s first create table with some records:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE dbo.tbl_First ( ID INTEGER IDENTITY(1,1) ,Name VARCHAR(250) ) GO INSERT INTO dbo.tbl_First(Name) VALUES('ABC'),('XYZ'),('ABC'),('EFG'),('PQR'),('EFG'),('RND'),('YFQ'),('XVK'),('ABC') GO SELECT *FROM dbo.tbl_First ORDER BY Name GO |
Result of above select statement is:
You can see ABC and EFG are duplicate name.
Create a second table with copy unique data from the first table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE TABLE dbo.tbl_Second ( ID INTEGER ,Name VARCHAR(250) ) GO INSERT INTO dbo.tbl_Second SELECT ID ,Name FROM ( SELECT ID ,Name ,ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID) AS RowNumber FROM dbo.tbl_First AS T )AS T WHERE T.RowNumber = 1 GO SELECT *FROM dbo.tbl_Second GO |
The result of above second table is:
Now you can see all names are unique and copied only seven unique records.
