Klausa OUTPUT (Transact-SQL)
Berlaku untuk: SQL ServerAzure SQL Database Azure SQL Managed Instance
Mengembalikan informasi dari, atau ekspresi berdasarkan, setiap baris yang terpengaruh oleh pernyataan INSERT, UPDATE, DELETE, atau MERGE. Hasil ini dapat dikembalikan ke aplikasi pemrosesan untuk digunakan dalam hal-hal seperti pesan konfirmasi, pengarsipan, dan persyaratan aplikasi lainnya. Hasilnya juga dapat disisipkan ke dalam tabel atau variabel tabel. Selain itu, Anda dapat mengambil hasil OUTPUT
klausa dalam pernyataan INSERT, UPDATE, DELETE, atau MERGE berlapis, dan menyisipkan hasil tersebut ke dalam tabel atau tampilan target.
Catatan
Pernyataan UPDATE, INSERT, atau DELETE yang memiliki OUTPUT
klausul akan mengembalikan baris ke klien meskipun pernyataan mengalami kesalahan dan digulung balik. Hasilnya tidak boleh digunakan jika terjadi kesalahan saat Anda menjalankan pernyataan.
Digunakan dalam:
Sintaksis
<OUTPUT_CLAUSE> ::=
{
[ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ]
[ OUTPUT <dml_select_list> ]
}
<dml_select_list> ::=
{ <column_name> | scalar_expression } [ [ AS ] column_alias_identifier ]
[ , ...n ]
<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
| $action
Catatan
Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.
Argumen
@table_variable
Menentukan variabel tabel tempat baris yang dikembalikan disisipkan ke dalam alih-alih dikembalikan ke pemanggil. @table_variable harus dideklarasikan sebelum pernyataan INSERT, UPDATE, DELETE, atau MERGE.
Jika column_list tidak ditentukan, variabel tabel harus memiliki jumlah kolom yang sama dengan tataan hasil OUTPUT
. Pengecualiannya adalah identitas dan kolom komputasi, yang harus dilewati. Jika column_list ditentukan, kolom apa pun yang dihilangkan harus mengizinkan nilai null atau memiliki nilai default yang ditetapkan untuk kolom tersebut.
Untuk informasi selengkapnya tentang variabel tabel , lihat tabel (Transact-SQL).
output_table
Menentukan tabel tempat baris yang dikembalikan disisipkan ke dalam alih-alih dikembalikan ke pemanggil. output_table mungkin tabel sementara.
Jika column_list tidak ditentukan, tabel harus memiliki jumlah kolom yang sama dengan kumpulan hasil OUTPUT
. Pengecualiannya adalah identitas dan kolom komputasi. Ini harus dilewati. Jika column_list ditentukan, kolom apa pun yang dihilangkan harus mengizinkan nilai null atau memiliki nilai default yang ditetapkan untuk kolom tersebut.
output_table tidak dapat:
- Telah mengaktifkan pemicu yang ditentukan di atasnya.
- Berpartisipasi di salah satu sisi batasan KUNCI ASING.
- Memiliki batasan CHECK atau aturan yang diaktifkan.
column_list
Daftar nama kolom opsional pada tabel target klausa INTO. Ini dianalogikan dengan daftar kolom yang diizinkan dalam pernyataan INSERT .
scalar_expression
Kombinasi simbol dan operator apa pun yang mengevaluasi ke satu nilai. Fungsi agregat tidak diizinkan dalam scalar_expression.
Setiap referensi ke kolom dalam tabel yang sedang dimodifikasi harus memenuhi syarat dengan awalan INSERTED atau DELETED.
column_alias_identifier
Nama alternatif yang digunakan untuk mereferensikan nama kolom.
DIHAPUS
Awalan kolom yang menentukan nilai yang dihapus oleh operasi pembaruan atau penghapusan. Kolom yang diawali dengan DELETED mencerminkan nilai sebelum pernyataan UPDATE, DELETE, atau MERGE selesai.
DELETED tidak dapat digunakan dengan OUTPUT
klausa dalam pernyataan INSERT.
DIMASUKKAN
Awalan kolom yang menentukan nilai yang ditambahkan oleh operasi sisipkan atau perbarui. Kolom yang diawali dengan INSERTED mencerminkan nilai setelah pernyataan UPDATE, INSERT, atau MERGE selesai tetapi sebelum pemicu dijalankan.
INSERTED tidak dapat digunakan dengan OUTPUT
klausa dalam pernyataan DELETE.
from_table_name
Awalan kolom yang menentukan tabel yang disertakan dalam klausa FROM dari pernyataan DELETE, UPDATE, atau MERGE yang digunakan untuk menentukan baris yang akan diperbarui atau dihapus.
Jika tabel yang sedang dimodifikasi juga ditentukan dalam klausa FROM, referensi apa pun ke kolom dalam tabel tersebut harus memenuhi syarat dengan awalan INSERTED atau DELETED.
*
*
menentukan bahwa semua kolom yang terpengaruh oleh tindakan hapus, sisipkan, atau perbarui dikembalikan dalam urutan di mana kolom tersebut ada dalam tabel.
Misalnya, OUTPUT DELETED.*
dalam pernyataan DELETE berikut mengembalikan semua kolom yang dihapus dari ShoppingCartItem
tabel:
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.*;
column_name
Referensi kolom eksplisit. Setiap referensi ke tabel yang sedang dimodifikasi harus memenuhi syarat dengan benar dengan prefiks INSERTED atau DELETED yang sesuai, misalnya: INSERTED.column_name.
$action
Hanya tersedia untuk pernyataan MERGE. Menentukan kolom jenis nvarchar(10) dalam OUTPUT
klausa dalam pernyataan MERGE yang mengembalikan salah satu dari tiga nilai untuk setiap baris: 'INSERT', 'UPDATE', atau 'DELETE', sesuai dengan tindakan yang dilakukan pada baris tersebut.
Keterangan
Klausa OUTPUT <dml_select_list>
dan OUTPUT <dml_select_list> INTO { @table_variable | output_table }
klausa dapat ditentukan dalam satu pernyataan INSERT, UPDATE, DELETE, atau MERGE.
Catatan
Kecuali ditentukan sebaliknya, referensi ke OUTPUT
klausul mengacu pada OUTPUT
klausul dan OUTPUT INTO
klausa.
Klausa OUTPUT
mungkin berguna untuk mengambil nilai identitas atau kolom komputasi setelah operasi INSERT atau UPDATE.
Saat kolom komputasi disertakan dalam <dml_select_list>
, kolom terkait dalam tabel output atau variabel tabel bukan kolom komputasi. Nilai dalam kolom baru adalah nilai yang dihitung pada saat pernyataan dijalankan.
Tidak ada jaminan bahwa urutan perubahan diterapkan ke tabel, dan urutan di mana baris disisipkan ke dalam tabel output atau variabel tabel, akan sesuai.
Jika parameter atau variabel dimodifikasi sebagai bagian dari pernyataan UPDATE, OUTPUT
klausul selalu mengembalikan nilai parameter atau variabel seperti sebelum pernyataan dijalankan alih-alih nilai yang dimodifikasi.
Anda dapat menggunakan OUTPUT
dengan pernyataan UPDATE atau DELETE yang diposisikan pada kursor yang menggunakan sintaks WHERE CURRENT OF.
Klausa OUTPUT
tidak didukung dalam pernyataan berikut:
Pernyataan DML yang mereferensikan tampilan partisi lokal, tampilan partisi terdistribusi, atau tabel jarak jauh.
Pernyataan INSERT yang berisi pernyataan EXECUTE.
Predikat teks lengkap tidak diizinkan dalam
OUTPUT
klausul saat tingkat kompatibilitas database diatur ke 100.Klausa
OUTPUT INTO
tidak dapat digunakan untuk menyisipkan ke dalam tampilan, atau fungsi set baris.Fungsi yang ditentukan pengguna tidak dapat dibuat jika berisi
OUTPUT
klausa INTO yang memiliki tabel sebagai targetnya.
Untuk mencegah perilaku nondeterministik, OUTPUT
klausul tidak dapat berisi referensi berikut:
Subkueri atau fungsi yang ditentukan pengguna yang melakukan akses data pengguna atau sistem, atau diasumsikan untuk melakukan akses tersebut. Fungsi yang ditentukan pengguna diasumsikan untuk melakukan akses data jika tidak terikat skema.
Kolom dari tampilan atau fungsi bernilai tabel sebaris saat kolom tersebut ditentukan oleh salah satu metode berikut:
Subkueri.
Fungsi yang ditentukan pengguna yang melakukan akses data pengguna atau sistem, atau diasumsikan untuk melakukan akses tersebut.
Kolom komputasi yang berisi fungsi yang ditentukan pengguna yang melakukan akses data pengguna atau sistem dalam definisinya.
Ketika SQL Server mendeteksi kolom seperti itu
OUTPUT
dalam klausul, kesalahan 4186 dimunculkan.
Menyisipkan data yang dikembalikan dari klausa OUTPUT ke dalam tabel
Saat Anda menangkap hasil OUTPUT
klausa dalam pernyataan INSERT, UPDATE, DELETE, atau MERGE berlapis dan menyisipkan hasil tersebut ke dalam tabel target, ingatlah informasi berikut:
Seluruh operasi adalah atomik. Baik pernyataan INSERT maupun pernyataan DML berlapis yang berisi
OUTPUT
klausul yang dijalankan, atau seluruh pernyataan gagal.Pembatasan berikut berlaku untuk target pernyataan INSERT luar:
Target tidak boleh berupa tabel jarak jauh, tampilan, atau ekspresi tabel umum.
Target tidak dapat memiliki batasan KUNCI ASING, atau dirujuk oleh batasan KUNCI ASING.
Pemicu tidak dapat ditentukan pada target.
Target tidak dapat berpartisipasi dalam replikasi penggabungan atau langganan yang dapat diperbarui untuk replikasi transaksional.
Pembatasan berikut berlaku untuk pernyataan DML berlapis:
Target tidak boleh berupa tabel jarak jauh atau tampilan yang dipartisi.
Sumber itu sendiri tidak dapat berisi
<dml_table_source>
klausul.
Klausa
OUTPUT INTO
tidak didukung dalam pernyataan INSERT yang berisi<dml_table_source>
klausa.@@ROWCOUNT
mengembalikan baris yang disisipkan hanya oleh pernyataan INSERT luar.@@IDENTITY
, ,SCOPE_IDENTITY
danIDENT_CURRENT
mengembalikan nilai identitas yang dihasilkan hanya oleh pernyataan DML berlapis, dan bukan yang dihasilkan oleh pernyataan INSERT luar.Pemberitahuan kueri memperlakukan pernyataan sebagai entitas tunggal, dan jenis pesan apa pun yang dibuat akan menjadi jenis DML berlapis, bahkan jika perubahan signifikan berasal dari pernyataan INSERT luar itu sendiri.
<dml_table_source>
Dalam klausa, klausa SELECT dan WHERE tidak dapat menyertakan subkueri, fungsi agregat, fungsi peringkat, predikat teks lengkap, fungsi yang ditentukan pengguna yang melakukan akses data, atauTEXTPTR()
fungsi.
Paralelisme
OUTPUT
Klausa yang mengembalikan hasil ke klien, atau variabel tabel, akan selalu menggunakan rencana serial.
Dalam konteks database yang diatur ke tingkat kompatibilitas 130 atau lebih tinggi, jika INSERT...SELECT
operasi menggunakan WITH (TABLOCK)
petunjuk untuk pernyataan SELECT dan juga menggunakan untuk menyisipkan ke dalam tabel sementara atau pengguna, maka tabel target untuk INSERT...SELECT
akan memenuhi syarat untuk paralelisme OUTPUT...INTO
tergantung pada biaya subtree. Tabel target yang OUTPUT INTO
direferensikan dalam klausul tidak akan memenuhi syarat untuk paralelisme.
Pemicu
Kolom yang dikembalikan dari OUTPUT
mencerminkan data seperti setelah pernyataan INSERT, UPDATE, atau DELETE selesai tetapi sebelum pemicu dijalankan.
Untuk PEMICU ALIH-ALIH, hasil yang dikembalikan dihasilkan seolah-olah INSERT, UPDATE, atau DELETE benar-benar terjadi, bahkan jika tidak ada modifikasi yang terjadi sebagai hasil dari operasi pemicu. Jika pernyataan yang menyertakan OUTPUT
klausul digunakan di dalam isi pemicu, alias tabel harus digunakan untuk mereferensikan pemicu tabel yang disisipkan dan dihapus untuk menghindari referensi kolom duplikat dengan tabel INSERTED dan DELETED yang terkait dengan OUTPUT
.
OUTPUT
Jika klausa ditentukan tanpa juga menentukan kata kunci INTO, target operasi DML tidak dapat memiliki pemicu yang diaktifkan yang ditentukan di atasnya untuk tindakan DML yang diberikan. Misalnya, jika OUTPUT
klausul ditentukan dalam pernyataan UPDATE, tabel target tidak dapat memiliki pemicu UPDATE yang diaktifkan.
sp_configure
Jika opsi melarang hasil dari pemicu diatur, OUTPUT
klausa tanpa klausa INTO menyebabkan pernyataan gagal saat dipanggil dari dalam pemicu.
Jenis data
Klausa OUTPUT
ini mendukung jenis data objek besar: nvarchar(max), varchar(max), varbinary(max), text, ntext, image, dan xml. Saat Anda menggunakan .WRITE
klausa dalam pernyataan UPDATE untuk memodifikasi kolom nvarchar(max), varchar(max), atau varbinary(max), gambar lengkap sebelum dan sesudah nilai dikembalikan jika direferensikan. Fungsi TEXTPTR()
tidak dapat muncul sebagai bagian dari ekspresi pada kolom teks, ntext, atau gambar dalam OUTPUT
klausa.
Antrean
Anda dapat menggunakan OUTPUT
dalam aplikasi yang menggunakan tabel sebagai antrean, atau untuk menyimpan tataan hasil menengah. Artinya, aplikasi terus menambahkan atau menghapus baris dari tabel. Contoh berikut menggunakan klausul OUTPUT
dalam pernyataan DELETE untuk mengembalikan baris yang dihapus ke aplikasi panggilan.
USE AdventureWorks2022;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT DELETED.*
WHERE DatabaseLogID = 7;
GO
Contoh ini menghapus baris dari tabel yang digunakan sebagai antrean dan mengembalikan nilai yang dihapus ke aplikasi pemrosesan dalam satu tindakan. Semantik lain juga dapat diimplementasikan, seperti menggunakan tabel untuk mengimplementasikan tumpukan. Namun, SQL Server tidak menjamin urutan di mana baris diproses dan dikembalikan oleh pernyataan DML menggunakan OUTPUT
klausa . Terserah aplikasi untuk menyertakan klausul WHERE yang sesuai yang dapat menjamin semantik yang diinginkan, atau memahami bahwa ketika beberapa baris mungkin memenuhi syarat untuk operasi DML, tidak ada urutan yang dijamin. Contoh berikut menggunakan subkueri dan mengasumsikan keunikan adalah karakteristik DatabaseLogID
kolom untuk mengimplementasikan semantik pengurutan yang diinginkan.
USE tempdb;
GO
CREATE TABLE dbo.table1
(
id INT,
employee VARCHAR(32)
);
GO
INSERT INTO dbo.table1
VALUES (1, 'Fred'),
(2, 'Tom'),
(3, 'Sally'),
(4, 'Alice');
GO
DECLARE @MyTableVar TABLE (
id INT,
employee VARCHAR(32)
);
PRINT 'table1, before delete';
SELECT *
FROM dbo.table1;
DELETE
FROM dbo.table1
OUTPUT DELETED.*
INTO @MyTableVar
WHERE id = 4
OR id = 2;
PRINT 'table1, after delete';
SELECT *
FROM dbo.table1;
PRINT '@MyTableVar, after delete';
SELECT *
FROM @MyTableVar;
DROP TABLE dbo.table1;
Berikut hasilnya:
table1, before delete
id employee
----------- ------------------------------
1 Fred
2 Tom
3 Sally
4 Alice
table1, after delete
id employee
----------- ------------------------------
1 Fred
3 Sally
@MyTableVar, after delete
id employee
----------- ------------------------------
2 Tom
4 Alice
Catatan
Gunakan petunjuk tabel READPAST dalam pernyataan UPDATE dan DELETE jika skenario Anda memungkinkan beberapa aplikasi untuk melakukan pembacaan yang merusak dari satu tabel. Ini mencegah masalah penguncian yang dapat muncul jika aplikasi lain sudah membaca rekaman kualifikasi pertama dalam tabel.
Izin
Izin SELECT diperlukan pada kolom apa pun yang diambil melalui <dml_select_list>
atau digunakan dalam <scalar_expression>
.
Izin INSERT diperlukan pada tabel apa pun yang ditentukan dalam <output_table>
.
Contoh
J. Menggunakan OUTPUT INTO dengan pernyataan INSERT
Contoh berikut menyisipkan baris ke ScrapReason
dalam tabel dan menggunakan klausul OUTPUT
untuk mengembalikan hasil pernyataan ke @MyTableVar
variabel tabel. ScrapReasonID
Karena kolom ditentukan dengan properti IDENTITY, nilai tidak ditentukan dalam pernyataan untuk kolom tersebutINSERT
. Namun, nilai yang dihasilkan oleh Mesin Database untuk kolom tersebut OUTPUT
dikembalikan dalam klausa di kolom INSERTED.ScrapReasonID
.
USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
NewScrapReasonID SMALLINT,
Name VARCHAR(50),
ModifiedDate DATETIME);
INSERT Production.ScrapReason
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
INTO @MyTableVar
VALUES (N'Operator error', GETDATE());
--Display the result set of the table variable.
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
GO
B. Menggunakan OUTPUT dengan pernyataan DELETE
Contoh berikut menghapus semua baris dalam ShoppingCartItem
tabel. Klausa OUTPUT DELETED.*
menentukan bahwa hasil DELETE
pernyataan, yaitu, semua kolom dalam baris yang dihapus, dikembalikan ke aplikasi panggilan. Pernyataan SELECT
yang mengikuti memverifikasi hasil operasi penghapusan pada ShoppingCartItem
tabel.
USE AdventureWorks2022;
GO
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.*
WHERE ShoppingCartID = 20621;
--Verify the rows in the table matching the WHERE clause have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem WHERE ShoppingCartID = 20621;
GO
C. Menggunakan OUTPUT INTO dengan pernyataan UPDATE
Contoh berikut memperbarui VacationHours
kolom dalam Employee
tabel sebesar 25 persen untuk 10 baris pertama. Klausa OUTPUT
VacationHours
mengembalikan nilai yang ada sebelum menerapkan UPDATE
pernyataan di kolom DELETED.VacationHours
, dan nilai yang diperbarui dalam kolom INSERTED.VacationHours
ke @MyTableVar
variabel tabel.
Dua SELECT
pernyataan mengikuti, yang mengembalikan nilai di @MyTableVar
dan hasil operasi pembaruan dalam Employee
tabel.
USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
EmpID INT NOT NULL,
OldVacationHours INT,
NewVacationHours INT,
ModifiedDate DATETIME);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
ModifiedDate = GETDATE()
OUTPUT INSERTED.BusinessEntityID,
DELETED.VacationHours,
INSERTED.VacationHours,
INSERTED.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
D. Menggunakan OUTPUT INTO untuk mengembalikan ekspresi
Contoh berikut dibangun pada contoh C dengan menentukan ekspresi dalam OUTPUT
klausul sebagai perbedaan antara nilai yang VacationHours
diperbarui VacationHours
dan nilai sebelum pembaruan diterapkan. Nilai ekspresi ini dikembalikan ke @MyTableVar
variabel tabel di kolom VacationHoursDifference
.
USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
EmpID INT NOT NULL,
OldVacationHours INT,
NewVacationHours INT,
VacationHoursDifference INT,
ModifiedDate DATETIME);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
ModifiedDate = GETDATE()
OUTPUT INSERTED.BusinessEntityID,
DELETED.VacationHours,
INSERTED.VacationHours,
INSERTED.VacationHours - DELETED.VacationHours,
INSERTED.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours,
VacationHoursDifference, ModifiedDate
FROM @MyTableVar;
GO
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
E. Menggunakan OUTPUT INTO dengan from_table_name dalam pernyataan UPDATE
Contoh berikut memperbarui ScrapReasonID
kolom dalam WorkOrder
tabel untuk semua perintah kerja dengan yang ditentukan ProductID
dan ScrapReasonID
. Klausa OUTPUT INTO
mengembalikan nilai dari tabel yang sedang diperbarui (WorkOrder
) dan juga dari Product
tabel. Tabel Product
digunakan dalam FROM
klausul untuk menentukan baris yang akan diperbarui. WorkOrder
Karena tabel memiliki pemicu yang AFTER UPDATE
ditentukan di atasnya, INTO
kata kunci diperlukan.
USE AdventureWorks2022;
GO
DECLARE @MyTestVar TABLE (
OldScrapReasonID INT NOT NULL,
NewScrapReasonID INT NOT NULL,
WorkOrderID INT NOT NULL,
ProductID INT NOT NULL,
ProductName NVARCHAR(50)NOT NULL);
UPDATE Production.WorkOrder
SET ScrapReasonID = 4
OUTPUT DELETED.ScrapReasonID,
INSERTED.ScrapReasonID,
INSERTED.WorkOrderID,
INSERTED.ProductID,
p.Name
INTO @MyTestVar
FROM Production.WorkOrder AS wo
INNER JOIN Production.Product AS p
ON wo.ProductID = p.ProductID
AND wo.ScrapReasonID= 16
AND p.ProductID = 733;
SELECT OldScrapReasonID, NewScrapReasonID, WorkOrderID,
ProductID, ProductName
FROM @MyTestVar;
GO
F. Menggunakan OUTPUT INTO dengan from_table_name dalam pernyataan DELETE
Contoh berikut menghapus baris dalam ProductProductPhoto
tabel berdasarkan kriteria pencarian yang FROM
ditentukan dalam klausul DELETE
pernyataan. Klausa OUTPUT
mengembalikan kolom dari tabel yang dihapus (DELETED.ProductID
, DELETED.ProductPhotoID
) dan kolom dari Product
tabel. Tabel ini digunakan dalam FROM
klausa untuk menentukan baris yang akan dihapus.
USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
ProductID INT NOT NULL,
ProductName NVARCHAR(50)NOT NULL,
ProductModelID INT NOT NULL,
PhotoID INT NOT NULL);
DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
p.Name,
p.ProductModelID,
DELETED.ProductPhotoID
INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p
ON ph.ProductID = p.ProductID
WHERE p.ProductModelID BETWEEN 120 and 130;
--Display the results of the table variable.
SELECT ProductID, ProductName, ProductModelID, PhotoID
FROM @MyTableVar
ORDER BY ProductModelID;
GO
G. Menggunakan OUTPUT INTO dengan jenis data objek besar
Contoh berikut memperbarui nilai parsial dalam DocumentSummary
, kolom dalam Production.Document
tabel, dengan menggunakan .WRITE
nvarchar(max)
klausa . Kata components
digantikan oleh kata features
dengan menentukan kata pengganti, lokasi awal (offset) kata yang akan diganti dalam data yang ada, dan jumlah karakter yang akan diganti (panjang). Contoh menggunakan klausul OUTPUT
untuk mengembalikan gambar DocumentSummary
sebelum dan sesudah kolom ke @MyTableVar
variabel tabel. Gambar lengkap sebelum dan sesudah DocumentSummary
kolom dikembalikan.
USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
SummaryBefore NVARCHAR(max),
SummaryAfter NVARCHAR(max));
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
OUTPUT DELETED.DocumentSummary,
INSERTED.DocumentSummary
INTO @MyTableVar
WHERE Title = N'Front Reflector Bracket Installation';
SELECT SummaryBefore, SummaryAfter
FROM @MyTableVar;
GO
H. Menggunakan OUTPUT dalam PEMicu ALIH-ALIH
Contoh berikut menggunakan klausa OUTPUT
dalam pemicu untuk mengembalikan hasil operasi pemicu. Pertama, tampilan dibuat pada ScrapReason
tabel, lalu pemicu INSTEAD OF INSERT
didefinisikan pada tampilan yang hanya Name
memungkinkan kolom tabel dasar untuk dimodifikasi oleh pengguna. Karena kolom ScrapReasonID
adalah IDENTITY
kolom dalam tabel dasar, pemicu mengabaikan nilai yang disediakan pengguna. Ini memungkinkan Mesin Database untuk secara otomatis menghasilkan nilai yang benar. Selain itu, nilai yang disediakan oleh pengguna untuk ModifiedDate
diabaikan dan diatur ke tanggal saat ini. Klausa OUTPUT
mengembalikan nilai yang benar-benar disisipkan ke ScrapReason
dalam tabel.
USE AdventureWorks2022;
GO
IF OBJECT_ID('dbo.vw_ScrapReason', 'V') IS NOT NULL
DROP VIEW dbo.vw_ScrapReason;
GO
CREATE VIEW dbo.vw_ScrapReason
AS
SELECT ScrapReasonID,
Name,
ModifiedDate
FROM Production.ScrapReason;
GO
CREATE TRIGGER dbo.io_ScrapReason ON dbo.vw_ScrapReason
INSTEAD OF INSERT
AS
BEGIN
--ScrapReasonID is not specified in the list of columns to be inserted
--because it is an IDENTITY column.
INSERT INTO Production.ScrapReason (
Name,
ModifiedDate
)
OUTPUT INSERTED.ScrapReasonID,
INSERTED.Name,
INSERTED.ModifiedDate
SELECT Name, GETDATE()
FROM INSERTED;
END
GO
INSERT vw_ScrapReason (
ScrapReasonID,
Name,
ModifiedDate
)
VALUES (
99,
N'My scrap reason',
'20030404'
);
GO
Berikut adalah tataan hasil yang dihasilkan pada 12 April 2004 ('2004-04-12'
). Perhatikan bahwa ScrapReasonIDActual
kolom dan ModifiedDate
mencerminkan nilai yang dihasilkan oleh operasi pemicu alih-alih nilai yang disediakan dalam INSERT
pernyataan.
ScrapReasonID Name ModifiedDate
------------- ---------------- -----------------------
17 My scrap reason 2004-04-12 16:23:33.050
I. Menggunakan OUTPUT INTO dengan identitas dan kolom komputasi
Contoh berikut membuat EmployeeSales
tabel lalu menyisipkan beberapa baris ke dalamnya menggunakan INSERT
pernyataan dengan SELECT
pernyataan untuk mengambil data dari tabel sumber. Tabel EmployeeSales
berisi kolom identitas (EmployeeID
) dan kolom komputasi (ProjectedSales
).
USE AdventureWorks2022;
GO
IF OBJECT_ID('dbo.EmployeeSales', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales (
EmployeeID INT IDENTITY(1, 5) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
FirstName NVARCHAR(20) NOT NULL,
CurrentSales MONEY NOT NULL,
ProjectedSales AS CurrentSales * 1.10
);
GO
DECLARE @MyTableVar TABLE (
EmployeeID INT NOT NULL,
LastName NVARCHAR(20) NOT NULL,
FirstName NVARCHAR(20) NOT NULL,
CurrentSales MONEY NOT NULL,
ProjectedSales MONEY NOT NULL
);
INSERT INTO dbo.EmployeeSales (
LastName,
FirstName,
CurrentSales
)
OUTPUT INSERTED.EmployeeID,
INSERTED.LastName,
INSERTED.FirstName,
INSERTED.CurrentSales,
INSERTED.ProjectedSales
INTO @MyTableVar
SELECT c.LastName,
c.FirstName,
sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE '2%'
ORDER BY c.LastName,
c.FirstName;
SELECT EmployeeID,
LastName,
FirstName,
CurrentSales,
ProjectedSales
FROM @MyTableVar;
GO
SELECT EmployeeID,
LastName,
FirstName,
CurrentSales,
ProjectedSales
FROM dbo.EmployeeSales;
GO
j. Menggunakan OUTPUT dan OUTPUT INTO dalam satu pernyataan
Contoh berikut menghapus baris dalam ProductProductPhoto
tabel berdasarkan kriteria pencarian yang FROM
ditentukan dalam klausul DELETE
pernyataan. Klausa OUTPUT INTO
mengembalikan kolom dari tabel yang dihapus (DELETED.ProductID
, DELETED.ProductPhotoID
) dan kolom dari Product
tabel ke @MyTableVar
variabel tabel. Tabel Product
digunakan dalam FROM
klausa untuk menentukan baris yang akan dihapus. Klausa OUTPUT
DELETED.ProductID
mengembalikan kolom , DELETED.ProductPhotoID
dan tanggal dan waktu baris dihapus dari ProductProductPhoto
tabel ke aplikasi panggilan.
USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
ProductID INT NOT NULL,
ProductName NVARCHAR(50) NOT NULL,
ProductModelID INT NOT NULL,
PhotoID INT NOT NULL
);
DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
p.Name,
p.ProductModelID,
DELETED.ProductPhotoID
INTO @MyTableVar
OUTPUT DELETED.ProductID,
DELETED.ProductPhotoID,
GETDATE() AS DeletedDate
FROM Production.ProductProductPhoto AS ph
INNER JOIN Production.Product AS p
ON ph.ProductID = p.ProductID
WHERE p.ProductID BETWEEN 800
AND 810;
--Display the results of the table variable.
SELECT ProductID,
ProductName,
PhotoID,
ProductModelID
FROM @MyTableVar;
GO
K. Menyisipkan data yang dikembalikan dari klausa OUTPUT
Contoh berikut mengambil data yang dikembalikan dari klausul MERGE
pernyataan, dan menyisipkan data tersebut OUTPUT
ke dalam tabel lain. Pernyataan memperbarui MERGE
Quantity
kolom ProductInventory
tabel setiap hari, berdasarkan pesanan yang diproses dalam SalesOrderDetail
tabel. Ini juga menghapus baris untuk produk yang inventorinya turun ke 0
atau kurang. Contoh mengambil baris yang dihapus dan menyisipkannya ke dalam tabel lain, ZeroInventory
, yang melacak produk tanpa inventori.
USE AdventureWorks2022;
GO
IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL
DROP TABLE Production.ZeroInventory;
GO
--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (
DeletedProductID INT,
RemovedOnDate DATETIME
);
GO
INSERT INTO Production.ZeroInventory (
DeletedProductID,
RemovedOnDate
)
SELECT ProductID,
GETDATE()
FROM (
MERGE Production.ProductInventory AS pi
USING (
SELECT ProductID,
SUM(OrderQty)
FROM Sales.SalesOrderDetail AS sod
INNER JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = '20070401'
GROUP BY ProductID
) AS src(ProductID, OrderQty)
ON (pi.ProductID = src.ProductID)
WHEN MATCHED
AND pi.Quantity - src.OrderQty <= 0
THEN
DELETE
WHEN MATCHED
THEN
UPDATE
SET pi.Quantity = pi.Quantity - src.OrderQty
OUTPUT $ACTION,
DELETED.ProductID
) AS Changes(Action, ProductID)
WHERE Action = 'DELETE';
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were inserted';
GO
SELECT DeletedProductID,
RemovedOnDate
FROM Production.ZeroInventory;
GO