SQL Server 2014 Insert Where NOT IN

Question

There are 2 tables. The first has fname (first name), lname (last name) and the second has many columns including: salutation (Mr, Dr etc), fname (first name), mname (middle name), lname (last name), a uniqueidentifier and a date.

I wish to create a third table containing the Salutation, fname, mname, lname, uid, added from the information in the other 2 tables then I will drop the first table and re-create the second table removing those columns.

This is what I have:

CREATE TABLE MyTable
(
    Id  int  IDENTITY (1, 1) PRIMARY KEY,
    Salutation  varchar(20) NULL DEFAULT (NULL),
    Fname  varchar(30) NOT NULL,
    Mname  varchar(30) NULL DEFAULT (NULL),
    Lname  varchar(30) NOT NULL,
    Uid  uniqueidentifier NULL DEFAULT (NULL),
    Added  Date NOT NULL DEFAULT (getdate())
);

INSERT INTO MyTable (Fname, LName)
   SELECT Fname, Lname 
   FROM TABLE1

This is where I am confused:

INSERT INTO MyTable (Salutation, Fname, Mname, LName, Uid, Added)
   SELECT 
      Salutation, Fname, Mname, LName, Uid, Added 
   FROM 
      Table2
   WHERE 
      Fname, Lname NOT IN (SELECT Fname, Lname FROM Table1)

Can someone please correct the last INSERT statement so that it works?

it doesn't like Fname, Lname to the right of the WHERE word
Should I insert from the second table first then from the first one?

potashin · Answer 1 · 2015-06-16T09:40:26.160

7

You can use not exists with a correlated subquery:

insert into MyTable (Salutation, Fname, Mname, LName, Uid, Added)
select Salutation, Fname, Mname, LName, Uid, Added from Table2 t
where not exists (select 1 from Table1 where Fname = t.Fname and Lname = t.Lname )

Update

According to the new conditions, provided by the OP in the comments below, I can determine 2 possible situations in which record should be included:

there is a fname and lname match;
there is a Salutation and lname match.

The query matching the conditions above is below :

insert into MyTable (Salutation, Fname, Mname, LName, Uid, Added)
select Salutation, Fname, Mname, LName, Uid, Added from Table2 t
where not exists (
   select 1 
   from Table1 
   where Lname = t.Lname and
       ( Fname = t.Fname or Salutation = t.Salutation )
)

edited Jun 16 '15 at 09:40

answered Apr 23 '15 at 22:40

potashin

44,205
11
83
107

I am finding that this approach misses over 1000 records. I am still playing with this suggestion of yours but no luck yet. I have noticed that Table2 has a fname value in some rows but no other name so I don't wish to include it. If it has a fname, lname add it, if it has a salutation and lname include it, if any row only has one of the Salutation or the names leaving the other 3 name fields free I don't want that result added. Having this little textbox to reply isn't very helpful either. – Apr 25 '15 at 01:41
@EssexMale : I've updated my answer, however I don't feel like it is 100% what you are seeking for, so I think that you should update your original post with a little more elaboration and clearer condition determination. – potashin Apr 25 '15 at 13:48

score 0 · Answer 2 · answered Apr 23 '15 at 23:41

0

With left join:

INSERT INTO MyTable (Salutation, Fname, Mname, LName, Uid, Added)
SELECT t2.Salutation, t2.Fname, t2.Mname, t2.LName, t2.Uid, t2.Added FROM Table2 t2
LEFT JOIN Table1 t1 on t1.Fname = t2.Fname AND t1.Lname = t2.Lname
WHERE t1.ID IS NULL

answered Apr 23 '15 at 23:41

Giorgi Nakeuri

35,155
8
47
75

score 0 · Answer 3 · answered Apr 24 '15 at 02:44

0

An in statement or a not in statement can only have one column so it could only be used like this

INSERT INTO MyTable (Salutation, Fname, Mname, LName, Uid, Added)
SELECT Salutation, Fname, Mname, LName, Uid, Added FROM Table2
WHERE Fname NOT IN (SELECT Fname FROM Table1)'

A better solution would be to use not exists or the left join like the other two uses showed you. I just thought I would explain why the not in statement would not work.

answered Apr 24 '15 at 02:44

Michelle

23
2

Hi Michelle, Before I asked the question I done what you suggested but it's no good to me because the WHERE only takes one column whereas I need it against the two and SQL gives some sort of message about only one can be used without Exists. I tried WHERE fname, lname... and SQL moaned. Thanks for the suggestion but unfortunately it doesn't help. What I am trying to do is use what you suggested on one insert, the LEFT JOIN approach then a Case to create a Temp Table with rows I need to delete but I need to be able to save it & not worked that out so far then run a DELETE from there – Apr 25 '15 at 01:33

SQL Server 2014 Insert Where NOT IN

3 Answers3