SQL Compare and determine both duplicate & non duplicate records in same table
I have the following table example data
Customer Location ID Attribute1 Attribute2
Cust1 Loc1 1 High None
Cust1 Loc1 2 High None
Cust1 Loc1 3 Low None
开发者_JAVA百科Based on above example data, the first 2 records are duplicate and the last one is non-duplicate. So, I need to create two tables, one for non-duplicate records and one for duplicate. The no. of Attribute columns shown here is just example, usually it's around 10 columns.
Table One
Customer Location ID Attribute1 Attribute2
Cust1 Loc1 1 High None
Table Two
Customer Location ID Attribute1 Attribute2
Cust1 Loc1 3 Low None
Can this be performed in one SQL query?
Thanks for any suggestions, Javid
you can do this very easily....
(I have run this below query in oracle database I am not sure abour sql server but in sql sentence point of view it is doing right operation )
insert all
when (Customer , Location , ID , Attribute1 , Attribute2) in
(select Customer , Location , ID , Attribute1 , Attribute2
from base_table
group by Customer , Location , ID , Attribute1 , Attribute2
having count(*)>1) then
into Table_One (Customer , Location , ID , Attribute1 , Attribute2)
values (Customer , Location , ID , Attribute1 , Attribute2)
else
into table_two (Customer , Location , ID , Attribute1 , Attribute2 )
value (Customer , Location , ID , Attribute1 , Attribute2 )
select distinct Customer , Location , ID , Attribute1 , Attribute2 from base_table
this query is useful when you have all column values duplicate...
but in your question what you have given as example data you don't have duplicate ROWS..
see ID column values..
please tell here if you want to check for specific column group for the same..
精彩评论