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

No comments:

Post a Comment