编写存储过程以比较两个表值并在另一个表中插入值 [英] write the store procedure for comparing two table values and insert values in another table

问题描述

教育（表名）

Studid手机

1

2



学生（表名）

Studid

1

2

3

4



临时（表名）

Studid Mobilenum

1 9789512450

2 9789512650

3 9789455564

4 9785654450

5 9785657888

6 9789518780





我要比较Temptable和Studenttable studid。

如果Temptable和Studenttable studid匹配更新Studenteducation表中特定studid的mobileno



如果Temptable和Studenttable studid不匹配，请在Studenteducation表中插入学生ID和特定studid的mobileno。



为什么我如何编写商店程序并在Studenteducation表中插入和更新这些值。



Rgds，

Narasiman P.

解决方案

您好，

请在SP下方尝试，如果有帮助请告诉我。

  CREATE   PROCEDURE  sp_update_student_mobile 
  AS  
 < span class =code-keyword> BEGIN  
  MERGE 教育 AS  edu 
  USING （ SELECT  t.StudId，MobileNum  FROM  TempTable t  LEFT   JOIN 学生s  ON  t.StudId = s.StudId） AS  tmp 
  ON  edu.StudId = tmp.StudId 
  WHEN   MATCHED   THEN  更新  SET  edu.MobileNo = tmp.MobileNum 
  WHEN   NOT  < span class =code-keyword> MATCHED  那么 
  INSERT （StudId ，MobileNo）
  VALUES （tmp.StudId，tmp.MobileNum）; 
  END  

谢谢，

Hitesh Varde

尝试以下方法。



适用于Temp Table和Student table匹配的情况

 更新 t1 
  SET  t1.Mobile = t2 .MobileNum 
  FROM  Edutcation  AS  t1 
  INNER   JOIN 学生 AS  t2  ON  t1.Studid = t2.Studid 
  INNER   JOIN  TemporaryTbl  AS  t3  ON  t1.Studid = t3.Studid 

对于Temp Table和Student表不匹配的情况

  INSERT   INTO 教育（StudId，Mobile）
  SELECT  StudID，MobileNum 
  FROM  TempTbl T 
  LEFT   OUTER   JOIN 学生S  ON  T.StudID = S.StudID 
  WHERE  S.StudID    null  

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.

解决方案

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

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

Try below approach.

For case where Temp Table and Student table matched

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

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

这篇关于编写存储过程以比较两个表值并在另一个表中插入值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！