CODEWITHSUNDEEP
sql-serverselectdistinct
J.Lush
J.Lush

Reputation: 25

sql server select distinct with ID

I want to write a select distinct statement like this: 

INSERT INTO  Table2(ID, ColA, ColB, ColC) 
SELECT DISTINCT ID,ColA, ColB, ColC
FROM Table1

What happens now is that all data is copied because of the ID field. Can i create an insert into statement with a select distinct query which selects different data from ColA,ColB,ColC, but also inserts the ID field into Table2 without using it for the Select distinct query?

Greetings

Upvotes: 2

Views: 4628

Answers (3)

Ullas
Ullas

Reputation: 11556

Use CTE.

Query

;with cte as
(
    select rn = row_number() over
    (
        partition by cola,colb,colc
        order by id
    ),*
    from Table1
)
insert into Table2(id,cola,colb,colc)
select id,cola,colb,colc
from cte
where rn = 1;

SQL Fiddle

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93694

Another way would be using Identity function.

SELECT DISTINCT IDENTITY(int, 1, 1) AS id,
                    ColA,
                    ColB,
                    ColC
    INTO   Table2
    FROM   Table1

Note : The IDENTITY function can only be used when the SELECT statement has an INTO clause. You cannot use this in Insert ..into

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

You can try to make ID field of your table2 as primary key or identity column and then using a GROUP BY you can try this:

INSERT INTO Table2(ColA, ColB, ColC) 
SELECT ColA, ColB, ColC FROM Table1
GROUP BY ColA, ColB, ColC

Upvotes: 1

Related Questions