Friday, August 6, 2010

MS-SQL - Find the Missing Records from Source table and Insert Missing Records into Destination Table

Suppose i have two table in one database.
One is Student Table,it is source table,
Second is TempStudent Table,it is Destination table.

I want to compare the data in two table to find all the missing record on source table and insert all missing record into destination table.

Step 1
First Create a Database with Tables,it is look like this

   1:  USE A
   2:   
   3:  CREATE DATABASE A
   4:   
   5:  CREATE TABLE Student
   6:  (
   7:      StudentID NUMERIC(18,0) IDENTITY(1,1) PRIMARY KEY,
   8:      FirstName VARCHAR(50),
   9:      LastName VARCHAR(50) 
  10:  )
  11:   
  12:  CREATE TABLE TempStudent
  13:  (
  14:      StudentID NUMERIC(18,0) IDENTITY(1,1) PRIMARY KEY,
  15:      FirstName VARCHAR(50),
  16:      LastName VARCHAR(50) 
  17:  )

Step 2
Insert a two records in TempStudent table,it is look like this;


   1:  INSERT INTO TempStudent
   2:  (FirstName,LastName)
   3:  VALUES
   4:  ('Kishor','Naik') 
   5:   
   6:  INSERT INTO TempStudent 
   7:  (FirstName,LastName)
   8:  VALUES
   9:  ('Ramdas','Bhosale') 

Insert a five records in Student table,it is look like this;
   1:  INSERT INTO Student
   2:  (FirstName,LastName)
   3:  VALUES
   4:  ('Kishor','Naik') 
   5:   
   6:  INSERT INTO Student 
   7:  (FirstName,LastName)
   8:  VALUES
   9:  ('Ramdas','Bhosale')
The Below Data is missing records
   1:  INSERT INTO Student 
   2:  (FirstName,LastName)
   3:  VALUES
   4:  ('Sachin','G') 
   5:  INSERT INTO Student
   6:  (FirstName,LastName)
   7:  VALUES
   8:  ('Vikas','Bharti') 
   9:  INSERT INTO Student 
  10:  (FirstName,LastName)
  11:  VALUES
  12:  ('Shilpa','G')

Step 3
Find all missing record on Source Table by using EXCEPT keyword.It will compare the two tables and find all the missing record from source table.

   1:  SELECT Student.StudentID,Student.FirstName,Student.LastName
   2:      FROM Student
   3:  EXCEPT
   4:  SELECT TempStudent.StudentID,TempStudent.FirstName,TempStudent.LastName
   5:      FROM TempStudent

EXCEPT keyword - EXCEPT returns any distinct values from the left query that are not also found on the right query.

It will return the all the missing records.

Step 4
Now i want all the missing Records insert into Destination table.
   1:  INSERT INTO TempStudent
   2:  SELECT Student.FirstName,Student.LastName
   3:      FROM Student
   4:  EXCEPT
   5:  SELECT TempStudent.FirstName,TempStudent.LastName
   6:      FROM TempStudent 
Step 5
Now view the both the table
 
   1:   
   2:  SELECT * FROM Student
   3:   
   4:  SELECT * FROM TempStudent

1 comment:

  1. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a .Net developer learn from .Net Core Training in Chennai. or learn thru .Net Core Training in Chennai. Nowadays Dot Net has tons of job opportunities on various vertical industry.
    or Es6 Training in Chennai. Nowadays JavaScript has tons of job opportunities on various vertical industry.

    ReplyDelete