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

nuypyhwy 于 2023-02-21 发布在 SQL Server

关注(0)|答案(2)|浏览(239)

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'

sql-server

来源：https://stackoverflow.com/questions/75425972/how-to-insert-distinct-records-from-one-table-to-another-table-in-sql-server

2条答案

按热度按时间

o8x7eapl1#

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 null s are filtered out by country <> '' .

赞(0）回复(0）举报 2023-02-21

rqenqsqc2#

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

赞(0）回复(0）举报 2023-02-21

我来回答

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

2条答案

相关问题

热门标签

最新问答