CODEWITHSUNDEEP
sqlsql-serversql-server-2008
w3n2u
w3n2u

Reputation: 333

How to insert multiple values into a Row if 1 field is distinct

Right I have the following data which i need to insert into a table called locals but I only want to insert it if the street field is not already present in the locals table. The data and fields are as follows:

Street    PC       Locality
------------------------------
Street1   ABC xyz  A
Street2   DEF xyz  B

And so on but I want to insert into the Locals table if the Street field is not already present in the locals table.

I was thinking of using the following:

INSERT
  INTO Locals (Street,PC,Locality) 
       (
            SELECT DISTINCT s.Street
              FROM Locals_bk s
             WHERE NOT EXISTS (
                      SELECT 1
                        FROM Locals l
                       WHERE s.Street = l.Street
                   )
       )
     ;

But I realize that will only insert the street field not the rest of the data on the same row.

Upvotes: 0

Views: 83

Answers (4)

roman
roman

Reputation: 117606

insert into Locals (Street, PC, Locality) 
select b.Street, b.PC, b.Locality
from Locals_bk as b
where not exists (select * from Locals as t where t.street = b.street)

or

insert into Locals (Street, PC, Locality) 
select b.Street, b.PC, b.Locality
from Locals_bk as b
where b.street not in (select t.street from Locals as t)

Upvotes: 3

krishna kinnera
krishna kinnera

Reputation: 1543

You can use NOT IN :

    INSERT into Locals (Street,PC,Locality) 
    SELECT s.Street, s.PC, s.Locality
    FROM Locals_bk as s
    WHERE NOT IN (SELECT * FROM Locals as t WHERE t.street = s.street)

Upvotes: 1

Jodrell
Jodrell

Reputation: 35746

How about

INSERT [Locals]
SELECT
            [Street],
            [PC],
            [Locality]
    FROM
            [Locals_bk] bk
    WHERE
         NOT EXIST (
             SELECT * FROM [Locals] l WHERE l.[Street] = bk.[Street]
         );

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

The following fetches the needed fields from an arbitrary row that matches the street:

insert into Locals (Street, PC, Locality) 
select s.Street, s.PC, s.Locality
from (select s.*, row_number() over (partition by s.Street order by (select NULL)) as seqnum
      from Locals_bk s
     ) s
where seqnum = 1 and
     NOT EXISTS (SELECT 1
                  FROM Locals l
                  WHERE s.Street = l.Street);

If you know the values are always the same, you can just do:

insert into Locals (Street, PC, Locality) 
select distinct s.Street, s.PC, s.Locality
from Locals_bk s
where NOT EXISTS (SELECT 1
                  FROM Locals l
                  WHERE s.Street = l.Street);

Upvotes: 0

Related Questions