In this post, I provide you one 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 full demonstration:
Let’s first create table with some records:
CREATE TABLE dbo.tbl_First
ID INTEGER IDENTITY(1,1)
INSERT INTO dbo.tbl_First(Name) VALUES('ABC'),('XYZ'),('ABC'),('EFG'),('PQR'),('EFG'),('RND'),('YFQ'),('XVK'),('ABC')
SELECT *FROM dbo.tbl_First ORDER BY Name
Result of above select statement is:
Now let’s create a second table with copy unique data from the first table.
CREATE TABLE dbo.tbl_Second
INSERT INTO dbo.tbl_Second
,ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID) AS RowNumber
FROM dbo.tbl_First AS T
)AS T WHERE T.RowNumber = 1
SELECT *FROM dbo.tbl_Second
Now result of above second table is:
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.