write the store procedure for comparing two table values and insert values in another table

Question

0.00/5 (No votes)

See more:

Education (Table Name)
Studid Mobile
1
2

Student (Table Name)
Studid
1
2
3
4

Temporary (Table Name)
Studid Mobilenum
1 9789512450
2 9789512650
3 9789455564
4 9785654450
5 9785657888
6 9789518780

I want to compare the Temptable and Studenttable studid.
if Temptable and Studenttable studid matches update the mobileno of particular studid in Studenteducation table

if Temptable and Studenttable studid not matches insert the student id and mobileno of particular studid in Studenteducation table.

for that how can i write the store procedure and insert and update those values in Studenteducation table.

Rgds,
Narasiman P.

Posted 28-Apr-14 22:47pm

Member 8054539

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Hitesh Varde · Answer 1 · 2014-04-28T23:51:00

Hello,
Please try below SP and let me know if it helps.

SQL

CREATE PROCEDURE sp_update_student_mobile
AS
BEGIN
MERGE Education AS edu
USING (SELECT t.StudId,MobileNum FROM TempTable t LEFT JOIN Student s ON t.StudId = s.StudId) AS tmp
ON edu.StudId = tmp.StudId
WHEN MATCHED THEN UPDATE SET edu.MobileNo = tmp.MobileNum
WHEN NOT MATCHED THEN
INSERT(StudId,MobileNo)
VALUES(tmp.StudId,tmp.MobileNum);
END

Thanks,
Hitesh Varde

_Asif_ · Answer 2 · 2014-04-28T23:51:00

Try below approach.

For case where Temp Table and Student table matched

SQL

UPDATE t1
  SET t1.Mobile = t2.MobileNum
  FROM Edutcation  AS t1
  INNER JOIN Student AS t2 ON t1.Studid= t2.Studid
  INNER JOIN TemporaryTbl AS t3 ON t1.Studid= t3.Studid

For case where Temp Table and Student table does not match

SQL

INSERT INTO Education (StudId, Mobile)
SELECT StudID, MobileNum
FROM TempTbl T
LEFT OUTER JOIN Student S ON T.StudID = S.StudID
WHERE S.StudID is null

write the store procedure for comparing two table values and insert values in another table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0