All geek questions in one place

SQL Server 2014 Paste where NOT LOGIN

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

I want to create a third table containing salutation , fname , mname , lname , uid , added from the information in the other two tables, after which I will delete the first table and recreate the second table by deleting these 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 fix the last INSERT to make it work?

  • he doesn't like Fname, Lname right of the word WHERE
  • Should I insert from the second table first, and then from the first?
+6
source share
3 answers

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

In accordance with the new conditions provided by OP in the comments below, I can identify two possible situations in which the entry should be included:

  • there is a correspondence between fname and lname ;
  • There is a correspondence between Salutation and lname .

A request that meets the above conditions is given 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 ) )
+7
source

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
0
source

An an operator or an operator cannot contain only one column, so it can only be used as

 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 a non-existing or left join, as shown in two other applications. I just thought I would explain why not in a statement would not work.

0
source

Source: https://habr.com/ru/post/985827/

More articles:


All Articles