วิธีการทำ Upsert ใน SQL Server: การรวมการดำเนินการ INSERT และ UPDATE

เมื่อทำงานกับฐานข้อมูล เรามักจะพบสถานการณ์ที่เราต้องจัดการกับระเบียนที่อาจมีอยู่แล้วหรือไม่ นี่คือที่มาของแนวคิด Upsert ที่ช่วยให้เรารวมการดำเนินการ INSERT และ UPDATE ได้อย่างราบรื่น ในบทความบล็อกนี้ เราจะสำรวจความท้าทายทั่วไปใน SQL Server และให้คำตอบที่นำเสนอการทำ Upsert อย่างมีประสิทธิภาพ

ปัญหา: การจัดการการมอบหมายงาน

ลองนึกภาพว่าคุณมีวิวที่แสดงการมอบหมายงานที่ระบุว่าใครถูกมอบหมายให้กับงานแต่ละงานและสถานะปัจจุบันของแต่ละงาน เป้าหมายของคุณคือการสร้าง stored procedure ที่ส่งคืนจำนวนงานสำหรับสมาชิกแต่ละคนในสถานะต่างๆ

การพยายามแรกสุดเกี่ยวข้องกับการแทรกจำนวนลงในตารางชั่วคราว อย่างไรก็ตาม ปัญหาหลักเกิดขึ้นเมื่อคุณพบว่าสมาชิกบางคนที่มีงานในมากกว่าหนึ่งสถานะจะส่งผลให้คุณได้แถวที่แยกกันในตารางผลลัพธ์ แทนที่คุณจะต้องการวิธีที่สามารถปรับปรุงแถวที่มีอยู่สำหรับสมาชิกหรือแทรกแถวใหม่หากไม่พบ

นี่คือตัวอย่างที่เรียบง่ายของโค้ดที่คุณเริ่มต้นด้วย:

DECLARE @ResultTable table 
(
  StaffName nvarchar(100),
  Stage1Count int,
  Stage2Count int
)

INSERT INTO @ResultTable (StaffName, Stage1Count)
  SELECT StaffName, COUNT(*) FROM ViewJob
  WHERE InStage1 = 1
  GROUP BY StaffName

INSERT INTO @ResultTable (StaffName, Stage2Count)
  SELECT StaffName, COUNT(*) FROM ViewJob
  WHERE InStage2 = 1
  GROUP BY StaffName

ในสคริปต์นี้ สมาชิกที่มีงานในทั้งสองสถานะปรากฏสองครั้งใน @ResultTable ซึ่งไม่ใช่ผลลัพธ์ที่ต้องการ

โซลูชัน: การทำ Upsert

เพื่อจัดการกับปัญหานี้ เราจำเป็นต้องนำแนวทาง Upsert มาใช้ซึ่งรวมทั้งการแทรกและการอัปเดตระเบียนใน @ResultTable ต่อไปนี้คือขั้นตอนโดยละเอียดในการทำเช่นนั้น:

ขั้นตอนที่ 1: เริ่มต้นตารางผลลัพธ์ด้วยสมาชิกทั้งหมด

เริ่มต้นโดยการแทรกสมาชิกทั้งหมดลงในตารางผลลัพธ์ของคุณ โดยตั้งค่าจำนวนเริ่มต้นเป็นศูนย์ สิ่งนี้จะช่วยให้แน่ใจว่าสมาชิกแต่ละคนได้รับการแสดงในตาราง

INSERT INTO @ResultTable (StaffName, Stage1Count, Stage2Count)
SELECT StaffName, 0, 0 FROM ViewJob
GROUP BY StaffName

ขั้นตอนที่ 2: อัปเดตจำนวนของแต่ละสถานะ

ถัดไป เราจำเป็นต้องอัปเดตจำนวนสำหรับแต่ละสถานะ ดำเนินการคำสั่ง SQL ต่อไปนี้ ซึ่งใช้คำสั่ง UPDATE เพื่อกำหนดค่าตามจำนวนงานจากวิวของคุณ:

อัปเดตจำนวนสถานะ 1

UPDATE @ResultTable
SET Stage1Count = (
  SELECT COUNT(*) FROM ViewJob
  WHERE InStage1 = 1 AND @ResultTable.StaffName = StaffName
)

อัปเดตจำนวนสถานะ 2

UPDATE @ResultTable
SET Stage2Count = (
  SELECT COUNT(*) FROM ViewJob
  WHERE InStage2 = 1 AND @ResultTable.StaffName = StaffName
)

สรุป

โดยการทำตามขั้นตอนเหล่านี้ คุณจะสามารถทำการ Upsert ที่มีประสิทธิภาพใน SQL Server โดยไม่ต้องมีการใช้ cursor หรือการทำซ้ำที่ซับซ้อน วิธีนี้ช่วยให้คุณจัดการและบำรุงรักษาจำนวนงานที่ถูกต้องสำหรับสมาชิกแต่ละคนในสถานะต่างๆ ของฐานข้อมูลของคุณได้อย่างง่ายดาย

ความคิดสุดท้าย

การใช้วิธี Upsert ไม่เพียงแต่ช่วยเพิ่มประสิทธิภาพของคำสั่ง SQL ของคุณ แต่ยังทำให้ความพยายามในการจัดการฐานข้อมูลของคุณง่ายขึ้น การแสดงการมอบหมายงานของพนักงานได้อย่างชัดเจนจะช่วยปรับปรุงการวิเคราะห์ข้อมูลและผลลัพธ์การรายงานได้อย่างมาก

หากคุณมีคำถามหรือต้องการความช่วยเหลือเพิ่มเติมเกี่ยวกับคำสั่ง SQL ของคุณ โปรดอย่าลังเลที่จะถามในความคิดเห็นด้านล่าง!