CODEWITHSUNDEEP
sqlsql-server-2008
w3n2u
w3n2u

Reputation: 333

Insert the value from one table into another

I have the following SQL. I am trying to insert the ClientName from Clients into the pName column in CommisionPickups. I need to make sure that all the values are unique. 

insert into CommisionPickups (pName) 
   (select ClientName 
    from Clients  
    where ClientName <> CommisionPickups.pName)

Upvotes: 1

Views: 95

Answers (3)

Sadia Aziz
Sadia Aziz

Reputation: 46

here is a way to achieve it. I am working in SQL Server but i hope it will work for any Database Engine

    INSERT  INTO CommisionPickups ( pName )
    SELECT DISTINCT
            ClientName
    FROM    Client
    WHERE   ClientName NOT IN ( SELECT DISTINCT
                                        pName
                                FROM    CommisionPickups )

Upvotes: 1

abiansh
abiansh

Reputation: 79

i am not able to get your question but i understand partly may be you asking for this

if you want to insert distant value in pname than make pname as unique it will work automatically and select 

select  DISTINCT ClientName from Clients

take a look this one u can get

http://www.w3schools.com/sql/sql_select_into.asp

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726579

You cannot use <> to check uniqueness. To ensure that all names are unique, you need to make sure that you select unique names from CommisionPickups, and that the names you insert do not already exist in Clients. Use NOT EXISTS for CommisionPickups, and DISTINCT for Clients, like this:

insert into CommisionPickups (pName) 
(select DISTINCT c.ClientName from Clients c
where NOT EXISTS (SELECT 1 FROM CommisionPickups cp
                  WHERE c.ClientName = cp.pName)
)

Upvotes: 5

Related Questions