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')
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