Cara Melakukan Upsert di SQL Server: Menggabungkan Operasi Insert dan Update

Ketika bekerja dengan basis data, kita sering menemui situasi di mana kita perlu mengelola catatan yang mungkin sudah ada atau belum. Di sinilah konsep Upsert berperan, memungkinkan kita untuk secara mulus menggabungkan operasi INSERT dan UPDATE. Di pos blog ini, kita akan mengeksplorasi tantangan umum di SQL Server dan memberikan solusi yang menerapkan logika Upsert dengan efektif.

Masalah: Mengelola Penugasan Pekerjaan

Bayangkan Anda memiliki tampilan yang menampilkan penugasan pekerjaan, menunjukkan siapa yang ditugaskan untuk setiap pekerjaan dan tahap saat ini. Tujuan Anda adalah membuat prosedur tersimpan yang mengembalikan jumlah pekerjaan untuk setiap anggota staf di tahap yang berbeda.

Upaya awal melibatkan memasukkan jumlah ke dalam tabel sementara. Namun, masalah utama muncul ketika Anda menemukan bahwa anggota staf dengan pekerjaan di lebih dari satu tahap menghasilkan baris terpisah di tabel hasil Anda. Sebagai gantinya, Anda memerlukan metode yang akan memperbarui baris yang sudah ada untuk anggota staf atau memasukkan baris baru jika tidak ada yang ada.

Berikut adalah contoh sederhana dari kode yang Anda mulai:

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

Dalam cuplikan kode ini, seorang anggota staf dengan pekerjaan di kedua tahap muncul dua kali di @ResultTable, yang bukan merupakan hasil yang diinginkan.

Solusi: Melakukan Upsert

Untuk mengatasi masalah ini, kita perlu menerapkan pendekatan Upsert yang menggabungkan kedua operasi memasukkan dan memperbarui catatan dalam @ResultTable. Berikut adalah langkah-langkah untuk melakukannya:

Langkah 1: Inisialisasi Tabel Hasil dengan Semua Staf

Mulailah dengan memasukkan semua anggota staf ke dalam tabel hasil Anda dengan jumlah awal yang disetel ke nol. Ini memastikan bahwa setiap anggota staf terwakili dalam tabel.

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

Langkah 2: Perbarui Jumlah Tahap

Selanjutnya, kita perlu memperbarui jumlah untuk setiap tahap. Jalankan perintah SQL berikut, yang memanfaatkan pernyataan UPDATE untuk menetapkan nilai berdasarkan jumlah pekerjaan dari tampilan Anda:

Perbarui Jumlah Tahap 1

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

Perbarui Jumlah Tahap 2

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

Kesimpulan

Dengan mengikuti langkah-langkah ini, Anda mencapai operasi Upsert yang efektif di SQL Server tanpa perlu menggunakan kursor atau iterasi kompleks. Metode ini memungkinkan Anda untuk dengan mudah mengelola dan mempertahankan jumlah pekerjaan yang akurat untuk setiap anggota staf di berbagai tahap dalam basis data Anda.

Pemikiran Akhir

Memanfaatkan metodologi Upsert tidak hanya meningkatkan efisiensi kueri SQL Anda tetapi juga menyederhanakan upaya manajemen basis data Anda. Representasi yang jelas dari penugasan staf akan sangat meningkatkan analisis data dan hasil pelaporan.

Jika Anda memiliki pertanyaan atau memerlukan bantuan lebih lanjut dengan kueri SQL Anda, silakan hubungi kami di komentar di bawah!