开发者

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..

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜