Bagikan melalui

Klausa OUTPUT (Transact-SQL)

  • Artikel

Berlaku untuk: SQL ServerAzure SQL Database Azure SQL Managed Instance

Mengembalikan informasi dari, atau ekspresi berdasarkan, setiap baris yang INSERTdipengaruhi oleh pernyataan , , UPDATEDELETE, 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 klausul dalam pernyataan berlapis INSERT, , DELETEUPDATE, atau MERGE , 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:

Konvensi sintaks transact-SQL

Sintaks

<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

Argumen

@table_variable

Menentukan variabel tabel tempat baris yang dikembalikan disisipkan ke dalam alih-alih dikembalikan ke pemanggil. @table_variable harus dideklarasikan sebelum INSERTpernyataan , , UPDATEDELETE, 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.

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, 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.

output_table tidak dapat:

  • Telah mengaktifkan pemicu yang ditentukan di atasnya.
  • Berpartisipasi di salah satu sisi batasan FOREIGN KEY .
  • Memiliki CHECK batasan atau aturan yang diaktifkan.

column_list

Daftar nama kolom opsional pada tabel INTO target klausa. 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, dan nilai yang ada yang tidak berubah dengan operasi saat ini. Kolom yang diawali dengan DELETED mencerminkan nilai sebelum UPDATEpernyataan , DELETE, atau MERGE selesai.

DELETED tidak dapat digunakan dengan OUTPUT klausul dalam INSERT pernyataan.

DIMASUKKAN

Awalan kolom yang menentukan nilai yang ditambahkan oleh operasi sisipkan atau perbarui, dan nilai yang ada yang tidak berubah dengan operasi saat ini. Kolom yang diawali dengan INSERTED mencerminkan nilai setelah UPDATEpernyataan , , INSERTatau MERGE selesai tetapi sebelum pemicu dijalankan.

INSERTED tidak dapat digunakan dengan OUTPUT klausul dalam DELETE pernyataan.

from_table_name

Awalan kolom yang menentukan tabel yang disertakan dalam FROM klausul DELETE, , UPDATEatau MERGE pernyataan yang digunakan untuk menentukan baris yang akan diperbarui atau dihapus.

Jika tabel yang sedang dimodifikasi juga ditentukan dalam FROM klausul, referensi apa pun ke kolom dalam tabel tersebut harus memenuhi syarat dengan awalan INSERTED atau DELETED .

*

Tanda bintang (*) 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 berikut DELETE 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 INSERTED atau DELETED awalan yang sesuai, misalnya: INSERTED.<column_name>.

$action

Hanya tersedia untuk pernyataan tersebut MERGE . Menentukan kolom jenis nvarchar(10) dalam OUTPUT klausa dalam MERGE pernyataan yang mengembalikan salah satu dari tiga nilai untuk setiap baris: INSERT, , UPDATEatau DELETE, sesuai dengan tindakan yang dilakukan pada baris tersebut.

Keterangan

Klausa OUTPUT <dml_select_list> dan klausa dapat ditentukan dalam satu INSERTpernyataan , , UPDATEDELETE, atau MERGE OUTPUT <dml_select_list> INTO { @table_variable | output_table } .

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 INSERT operasi 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.

Urutan di mana perubahan diterapkan ke tabel, dan urutan di mana baris disisipkan ke dalam tabel output atau variabel tabel, tidak dijamin sesuai.

Jika parameter atau variabel dimodifikasi sebagai bagian UPDATE dari pernyataan, OUTPUT klausa selalu mengembalikan nilai parameter atau variabel seperti sebelum pernyataan dijalankan alih-alih nilai yang dimodifikasi.

Anda dapat menggunakan OUTPUT dengan pernyataan atau DELETE yang UPDATE diposisikan pada kursor yang menggunakan WHERE CURRENT OF sintaksis.

Klausa OUTPUT tidak didukung dalam pernyataan berikut:

  • Pernyataan DML yang mereferensikan tampilan partisi lokal, tampilan partisi terdistribusi, atau tabel jarak jauh.

  • INSERT pernyataan yang berisi EXECUTE pernyataan.

  • 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 INTO klausa 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 klausul dalam pernyataan berlapis INSERT, , UPDATE, DELETEatau MERGE dan menyisipkan hasil tersebut ke dalam tabel target, ingatlah informasi berikut:

  • Seluruh operasi adalah atomik. Baik pernyataan maupun INSERT pernyataan DML berlapis yang berisi OUTPUT klausa yang dijalankan, atau seluruh pernyataan gagal.

  • Pembatasan berikut berlaku untuk target pernyataan luar INSERT :

    • Target tidak boleh berupa tabel jarak jauh, tampilan, atau ekspresi tabel umum.

    • Target tidak dapat memiliki FOREIGN KEY batasan, atau dirujuk oleh FOREIGN KEY batasan.

    • 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 INSERT pernyataan yang berisi <dml_table_source> klausa.

  • @@ROWCOUNT mengembalikan baris yang disisipkan hanya oleh pernyataan luar INSERT .

  • @@IDENTITY, , SCOPE_IDENTITYdan IDENT_CURRENT mengembalikan nilai identitas yang dihasilkan hanya oleh pernyataan DML berlapis, dan bukan nilai yang dihasilkan oleh pernyataan luar INSERT .

  • Pemberitahuan kueri memperlakukan pernyataan sebagai entitas tunggal, dan jenis pesan apa pun yang dibuat adalah jenis DML berlapis, bahkan jika perubahan signifikan berasal dari pernyataan luar INSERT itu sendiri.

  • <dml_table_source> Dalam klausa, SELECT klausa dan WHERE tidak dapat mencakup subkueri, fungsi agregat, fungsi peringkat, predikat teks lengkap, fungsi yang ditentukan pengguna yang melakukan akses data, atau TEXTPTR() fungsi.

Paralelisme

OUTPUT Klausa yang mengembalikan hasil ke klien, atau variabel tabel, selalu menggunakan paket serial.

Dalam konteks database yang diatur ke tingkat kompatibilitas 130 atau lebih tinggi, jika INSERT...SELECT operasi menggunakan WITH (TABLOCK) petunjuk untuk pernyataan dan SELECT juga menggunakan untuk menyisipkan ke dalam tabel sementara atau pengguna, maka tabel target untuk INSERT...SELECT memenuhi syarat untuk paralelisme OUTPUT...INTO tergantung pada biaya subtree. Tabel target yang OUTPUT INTO direferensikan dalam klausul tidak memenuhi syarat untuk paralelisme.

Pemicu

Kolom yang dikembalikan dari OUTPUT mencerminkan data seperti setelah INSERTpernyataan , , UPDATEatau DELETE selesai, tetapi sebelum pemicu dijalankan.

Untuk INSTEAD OF pemicu, hasil yang dikembalikan dihasilkan seolah-olah INSERT, , UPDATEatau 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 INSERTED tabel dan DELETED yang terkait dengan OUTPUT.

OUTPUT Jika klausul ditentukan tanpa juga menentukan INTO kata kunci, 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 yang diaktifkan UPDATE .

sp_configure Jika opsi melarang hasil dari pemicu diatur, OUTPUT klausa tanpa INTO klausul 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 UPDATE pernyataan 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 DELETE pernyataan 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 mungkin juga 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 yang sesuai WHERE 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

READPAST Gunakan petunjuk tabel dalam UPDATE pernyataan dan DELETE jika skenario Anda memungkinkan beberapa aplikasi untuk melakukan bacaan yang merusak dari satu tabel. Ini mencegah masalah penguncian yang dapat muncul jika aplikasi lain sudah membaca rekaman kualifikasi pertama dalam tabel.

Izin

SELECT izin diperlukan pada kolom apa pun yang diambil melalui <dml_select_list> atau digunakan dalam <scalar_expression>.

INSERT izin diperlukan pada tabel apa pun yang ditentukan dalam <output_table>.

Contoh

Sampel kode Transact-SQL dalam artikel ini menggunakan AdventureWorks2022 database sampel atau AdventureWorksDW2022 , yang dapat Anda unduh dari halaman beranda Sampel Microsoft SQL Server dan Proyek Komunitas.

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 DocumentSummarykolom nvarchar (maks) dalam Production.Document tabel, dengan menggunakan .WRITE klausul . 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'). Kolom ScrapReasonIDActual 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.ProductIDmengembalikan 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

Konten terkait