Click here to Skip to main content
15,906,329 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
how can i remove all duplicate records from 4 columns in 4 different tables and keep one of them as original one.(doesn't matter which one)
also my tables are quite large so i would rather not inner join them.


What I have tried:

i have tried inner joining the tables but it takes a lot of time.
Posted
Updated 10-Jun-19 22:28pm
Comments
NithyaKumarJai 4-Jun-19 9:40am    
union is best way other wise try for partition based on Row number

Haver you looked at the SQL SELECT DISTINCT Statement[^]
 
Share this answer
 
Comments
taminhh 2-Jun-19 3:00am    
to use DISTINCT I have to join the tables. but since the tables are very large that would take a lot of time,I'm looking for a way to remove them without any kind of joining.
Further to your comment to Solution 1... as an alternative you could use UNION (Transact-SQL) - SQL Server | Microsoft Docs[^]
Note that UNION ALL selects all rows, but UNION will remove the duplicates for you e.g. with this simple test data
SQL
declare @table1 table (col1 int, col2 int, col3 int, col4 int)
insert into @table1 (col1, col2,col3,col4) values
(1,2,3,4),(2,3,4,5),(3,4,5,6),(4,5,6,7)

declare @table2 table (col1 int, col2 int, col3 int, col4 int)
insert into @table2 (col1, col2,col3,col4) values
(1,2,3,4),(2,3,4,5),(3,4,5,6),(5,5,6,7)

declare @table3 table (col1 int, col2 int, col3 int, col4 int)
insert into @table3 (col1, col2,col3,col4) values
(1,2,3,4),(2,3,4,5),(3,4,5,6),(4,5,6,8)

declare @table4 table (col1 int, col2 int, col3 int, col4 int)
insert into @table4 (col1, col2,col3,col4) values
(1,2,3,4),(2,3,4,5),(3,4,5,6),(4,5,6,7)
this query
SQL
select * from @table1
union all
select * from @table2
union all
select * from @table3
union all
select * from @table4
returns the results
col1	col2	col3	col4
1	2	3	4
2	3	4	5
3	4	5	6
4	5	6	7
1	2	3	4
2	3	4	5
3	4	5	6
5	5	6	7
1	2	3	4
2	3	4	5
3	4	5	6
4	5	6	8
1	2	3	4
2	3	4	5
3	4	5	6
4	5	6	7
but
SQL
select * from @table1
union
select * from @table2
union
select * from @table3
union
select * from @table4
returns
col1	col2	col3	col4
1	2	3	4
2	3	4	5
3	4	5	6
4	5	6	7
4	5	6	8
5	5	6	7
The next problem is if you have other columns in these tables that you want to retain, or if you want to retain 4 tables. If that is the case then you will need to provide more information - a minimum of some sample data and expected results
 
Share this answer
 
v2
Comments
NithyaKumarJai 4-Jun-19 9:40am    
union is best way other wise try for partition based on Row number
CHill60 4-Jun-19 9:46am    
Partition by Row Number would only work if the rows had the same row number in each table. The Partition would need to use the four columns that are duplicated. To handle all 4 tables it would have to be done on joined tables, which the OP does not want.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900