CODEWITHSUNDEEP
sqlsql-server
Bilal
Bilal

Reputation: 588

Insert unique values into same table

I have a table and I want to insert old values with new date but only the unique ones .

t1

ID      |    Block     |    Flats  |
1       |      1       |      GF-1 |
2       |      1       |      GF-2 |
3       |      2       |      GF-1 |
4       |      2       |      GF-2 |
5       |      2       |      GF-2 |

this is part of my table with some sample data I want it to become after copy

ID      |    Block     |    Flats  |
1       |      1       |      GF-1 |
2       |      1       |      GF-2 |
3       |      2       |      GF-1 |
4       |      2       |      GF-2 |
5       |      2       |      GF-2 |
6       |      1       |      GF-1 |
7       |      1       |      GF-2 |
8       |      2       |      GF-1 |
9       |      2       |      GF-2 |

As after copy it only copied the distinct values and GF-2 came only once. But when I tried

insert into t1 (ID,Block,Flats) select distinct Block,Flats from t1

It copies that GF-2 twice in block 2.

Note: ID column is incremented by 1 automatically.

Upvotes: 2

Views: 1993

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can generate all the rows using cross join, then weed out the ones that already exist:

insert into t1(block, flats)
    select b.block, f.flats
    from (select distinct block from t1) b cross join
         (select distinct flats from t1) f left join
         t1
         on t1.block = b.block and t1.flats = f.flats
    where t1.block is null;

Note: This assumes that id is an identity column (which is I see you have described as being the case).

Upvotes: 2

Related Questions