CODEWITHSUNDEEP
sql-serverselectwhere-clausedistinctsql-insert
Venkat
Venkat

Reputation: 2196

How to insert distinct records from one table to another table in SQL Server

I am using SQL Server and I have the following query

select distinct(country) 
from UserTable 
where country is not null 
  and country != '' 
order by country

It returns all distinct countries from the table and it is working properly.

Now from the returned values from this query, I want to insert into another table called lookuptable which contains the following columns:

LookupAttribute ="Region"
LookupDisplayValue = country name
LookupActualValue = country name

insert into LookupTable (LookupAttribute, LookupDisplayValue, LookupActualValue) 
    (select 'Region', distinct(country), distinct(country) 
     from UserTable  
     where country is not null and country != '' 
     order by Country)

However this does not work; I get an error:

Incorrect syntax near the keyword 'distinct'

Upvotes: 1

Views: 117

Answers (2)

forpas
forpas

Reputation: 164214

DISTINCT is not a function.
It operates on rows and not on columns.
Also, the ORDER BY clause does not guarantee the order of the insertions.

Change to this:

INSERT INTO LookupTable (LookupAttribute, LookupDisplayValue, LookupActualValue) 
SELECT DISTINCT 'Region', country, country 
FROM UserTable  
WHERE country <> '' ;

The condition country IS NOT NULL is not needed because nulls are filtered out by country <> ''.

Upvotes: 2

krhermit
krhermit

Reputation: 13

DISTINCT needs to go immediately after the SELECT and only used once.

select DISTINCT Region, country from UserTable  where country is not null and country!='' order by Country

Upvotes: 0

Related Questions