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

Question

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'

Answer 1

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