CODEWITHSUNDEEP
sqlsql-serversql-server-2012sql-insertexists
Pedro Emilio Vélez
Pedro Emilio Vélez

Reputation: 37

SQL Server: Insert record into a table if it doesn't exist

For the sake of simplicity, let's say I have two tables. One is called People and the other is called Contacting

People Table
ID primary key
NAME
WEBSITE
EMAIL

Contacting Table
ID (foreign key to People ID)
WAY_OF_CONTACTING
REFERENCE

The logic goes as follows: For every person there is in the People's table there could be several records on the Contacting's Table. Obviously, the relationship between People and Contacting is 1:many.

WAY_OF_CONTACTING could have only 2 values: "Email" and "Website" and the REFERENCE field contains the email or the website's name. The primary key of the Contacting table is a composiste primary key consisting of the ID and the Way_Of_Contacting. (This means, for every ID, they can only have up to one email and one website) The records on the people's table get deleted everynight and get populated throughout the day.

4 things could happen in the People table:

  1. Website and Email empty
  2. Website empty but not Email
  3. Email empty but not Email
  4. Neither Website nor Email Empty

My problem is the following:

For every record in the People table whos Website field is not empty I want to check if there exists a record in Contacting table in which the ID matches and Way_Of_Contacting='Website'

For every record in the People table whos Email field is not empty I want to check if there exists a record in Contacting table in which the ID matches and Way_Of_Contacting='Email'

In both of these cases, if the record doesn't exist, create a record. How could this be accomplished? I've tried using merge and if not exists but I can't seem to find a correct answer

Upvotes: 0

Views: 1032

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

For every record in the People table whos Website field is not empty I want to check if there exists a record in Contacting table in which the ID matches and Way_Of_Contacting='Website'

. . . if the record doesn't exist, create a record.

You can use not exists with insert. The example for "website" is:

insert into contacting (id, way_of_contacting, reference)
    select p.id, 'Website', p.website
    from people p
    where not exists (select 1
                      from contacting c
                      where c.id = p.id and
                            c.way_of_contacting = 'Website' and
                            c.reference = p.website
                     );

Similar logic is used for "email".

Upvotes: 1

Related Questions