Conditional Insert Query Based on Data in Target Table

Posted: June 23, 2013 by Transformer in MS Access, SQL
Tags: ,

Problem Statement: There is a table named Employee which has department wise employee details as given below. We want to insert some new records in this table but a record should not be inserted if it is already there in the table.


Dept	Employee_ID	Gender
IT	E123	          M
IT	E124	          M
IT	E125	          M
IT	E126	          M
IT	E127	          F
IT	E128	          F
HR	E129	          M
HR	E130	          F
HR	E131	          F
HR	E132	          F
HR	E133	          M

Solution: This can be done using the following query where we use EXISTS to check whether or not that record already exists.


INSERT INTO Employee (Dept,Employee_ID,Gender)

   SELECT  TOP 1 'HR' , 'E132','F' 
       FROM
         EMPLOYEE 
  WHERE Not EXISTS 
         (SELECT 1 
             FROM 
               Employee 
          WHERE Dept = 'HR' AND Employee_ID = 'E132'  AND Gender = 'F')

The above query will not insert the record in the table since it already exists.
Here TOP 1 has been used to create a single record to insert otherwise it will return multiple records(No of records in the Employee Table) with the same values.

Advertisements
Comments
  1. Very useful and dynamic…no needt to check data first then insert… great work guys..

  2. plerudulier says:

    Reblogged this on Repository of whatever concerns MS Access and commented:
    interesting, to be saved for potentially using it later. Thanks…. gyaan

  3. Mike says:

    Many thanks. This taught me that my query was working until multiple records contravened my no duplicate index. I expected the first insert to be accepted and the rest to be rejected – but Jet engine must insist that the full query completes without error or does nothing. TOP notch article :O) but it might be improved by using Select * (rather than Select 1) because I the value 1 looks to be significant in TOP 1 rather it being a straight forward count.

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s