Konstruktor Nilai Tabel (Transact-SQL)

Artikel
12/22/2023

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance

Menentukan sekumpulan ekspresi nilai baris yang akan dibangun ke dalam tabel. Konstruktor nilai tabel Transact-SQL memungkinkan beberapa baris data ditentukan dalam satu pernyataan DML. Konstruktor nilai tabel dapat ditentukan sebagai klausul VALUES dari INSERT ... Pernyataan VALUES, atau sebagai tabel turunan dalam klausa USING dari pernyataan MERGE atau klausa FROM.

Konvensi sintaks transact-SQL

Sintaks

VALUES ( <row value expression list> ) [ ,...n ]   
  
<row value expression list> ::=  
    {<row value expression> } [ ,...n ]  
  
<row value expression> ::=  
    { DEFAULT | NULL | expression }

Argumen

NILAI
Memperkenalkan daftar ekspresi nilai baris. Setiap daftar harus diapit tanda kurung dan dipisahkan oleh koma.

Jumlah nilai yang ditentukan dalam setiap daftar harus sama dan nilai harus dalam urutan yang sama dengan kolom dalam tabel. Nilai untuk setiap kolom dalam tabel harus ditentukan atau daftar kolom harus secara eksplisit menentukan kolom untuk setiap nilai masuk.

DEFAULT
Memaksa Mesin Database untuk menyisipkan nilai default yang ditentukan untuk kolom. Jika default tidak ada untuk kolom dan kolom mengizinkan nilai null, NULL akan disisipkan. DEFAULT tidak valid untuk kolom identitas. Ketika ditentukan dalam konstruktor nilai tabel, DEFAULT hanya diperbolehkan dalam pernyataan INSERT.

expression
Adalah konstanta, variabel, atau ekspresi. Ekspresi tidak boleh berisi pernyataan EXECUTE.

Batasan dan Pembatasan

Saat digunakan sebagai tabel turunan, tidak ada batasan jumlah baris.

Ketika digunakan sebagai klausa NILAI dari INSERT ... Pernyataan VALUES, ada batas 1000 baris. Kesalahan 10738 dikembalikan jika jumlah baris melebihi maksimum. Untuk menyisipkan lebih dari 1000 baris, gunakan salah satu metode berikut:

Membuat beberapa pernyataan INSERT
Menggunakan tabel turunan
Mengimpor data secara massal dengan menggunakan utilitas bcp, kelas .NET SqlBulkCopy, OPENROWSET (MASSAL ...), atau pernyataan SISIPAN MASSAL

Hanya nilai skalar tunggal yang diizinkan sebagai ekspresi nilai baris. Subkueri yang melibatkan beberapa kolom tidak diizinkan sebagai ekspresi nilai baris. Misalnya, kode berikut menghasilkan kesalahan sintaks karena daftar ekspresi nilai baris ketiga berisi subkueri dengan beberapa kolom.

USE AdventureWorks2022;  
GO  
CREATE TABLE dbo.MyProducts (Name VARCHAR(50), ListPrice MONEY);  
GO  
-- This statement fails because the third values list contains multiple columns in the subquery.  
INSERT INTO dbo.MyProducts (Name, ListPrice)  
VALUES ('Helmet', 25.50),  
       ('Wheel', 30.00),  
       (SELECT Name, ListPrice FROM Production.Product WHERE ProductID = 720);  
GO

Namun, pernyataan dapat ditulis ulang dengan menentukan setiap kolom dalam subkueri secara terpisah. Contoh berikut berhasil menyisipkan tiga baris ke MyProducts dalam tabel.

INSERT INTO dbo.MyProducts (Name, ListPrice)  
VALUES ('Helmet', 25.50),  
       ('Wheel', 30.00),  
       ((SELECT Name FROM Production.Product WHERE ProductID = 720),  
        (SELECT ListPrice FROM Production.Product WHERE ProductID = 720));  
GO

Jenis Data

Nilai yang ditentukan dalam pernyataan INSERT multi-baris mengikuti properti konversi jenis data dari sintaks UNION ALL. Ini menghasilkan konversi implisit dari jenis yang tidak cocok ke jenis prioritas yang lebih tinggi. Jika konversi bukan konversi implisit yang didukung, kesalahan akan dikembalikan. Misalnya, pernyataan berikut menyisipkan nilai bilangan bulat dan nilai karakter ke dalam kolom karakter jenis.

CREATE TABLE dbo.t (a INT, b CHAR);  
GO  
INSERT INTO dbo.t VALUES (1,'a'), (2, 1);  
GO

Saat pernyataan INSERT dijalankan, SQL Server mencoba mengonversi 'a' menjadi bilangan bulat karena prioritas tipe data menunjukkan bahwa bilangan bulat memiliki tipe yang lebih tinggi daripada karakter. Konversi gagal dan kesalahan dikembalikan. Anda dapat menghindari kesalahan dengan secara eksplisit mengonversi nilai yang sesuai. Misalnya, pernyataan sebelumnya dapat ditulis sebagai berikut.

INSERT INTO dbo.t VALUES (1,'a'), (2, CONVERT(CHAR,1));

Contoh

J. Menyisipkan beberapa baris data

Contoh berikut membuat tabel dbo.Departments lalu menggunakan konstruktor nilai tabel untuk menyisipkan lima baris ke dalam tabel. Karena nilai untuk semua kolom disediakan dan tercantum dalam urutan yang sama dengan kolom dalam tabel, nama kolom tidak harus ditentukan dalam daftar kolom.

USE AdventureWorks2022;  
GO  
INSERT INTO Production.UnitMeasure  
VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923'),
       (N'Y3', N'Cubic Yards', '20080923');  
GO

B. Menyisipkan beberapa baris dengan nilai DEFAULT dan NULL

Contoh berikut menunjukkan menentukan DEFAULT dan NULL saat menggunakan konstruktor nilai tabel untuk menyisipkan baris ke dalam tabel.

USE AdventureWorks2022;  
GO  
CREATE TABLE Sales.MySalesReason(  
SalesReasonID int IDENTITY(1,1) NOT NULL,  
Name dbo.Name NULL ,  
ReasonType dbo.Name NOT NULL DEFAULT 'Not Applicable' );  
GO  
INSERT INTO Sales.MySalesReason   
VALUES ('Recommendation','Other'), ('Advertisement', DEFAULT), (NULL, 'Promotion');  
  
SELECT * FROM Sales.MySalesReason;

C. Menentukan beberapa nilai sebagai tabel turunan dalam klausa FROM

Contoh berikut menggunakan konstruktor nilai tabel untuk menentukan beberapa nilai dalam klausa FROM dari pernyataan SELECT.

SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);  
GO  
-- Used in an inner join to specify values to return.  
SELECT ProductID, a.Name, Color  
FROM Production.Product AS a  
INNER JOIN (VALUES ('Blade'), ('Crown Race'), ('AWC Logo Cap')) AS b(Name)   
ON a.Name = b.Name;

D. Menentukan beberapa nilai sebagai tabel sumber turunan dalam pernyataan MERGE

Contoh berikut menggunakan MERGE untuk mengubah SalesReason tabel dengan memperbarui atau menyisipkan baris. Saat nilai NewName dalam tabel sumber cocok dengan nilai di Name kolom tabel target, (SalesReason), ReasonType kolom diperbarui dalam tabel target. Ketika nilai NewName tidak cocok, baris sumber disisipkan ke dalam tabel target. Tabel sumber adalah tabel turunan yang menggunakan konstruktor nilai tabel Transact-SQL untuk menentukan beberapa baris untuk tabel sumber.

USE AdventureWorks2022;  
GO  
-- Create a temporary table variable to hold the output actions.  
DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));  
  
MERGE INTO Sales.SalesReason AS Target  
USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))  
       AS Source (NewName, NewReasonType)  
ON Target.Name = Source.NewName  
WHEN MATCHED THEN  
UPDATE SET ReasonType = Source.NewReasonType  
WHEN NOT MATCHED BY TARGET THEN  
INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)  
OUTPUT $action INTO @SummaryOfChanges;  
  
-- Query the results of the table variable.  
SELECT Change, COUNT(*) AS CountPerChange  
FROM @SummaryOfChanges  
GROUP BY Change;

E. Menyisipkan lebih dari 1000 baris

Contoh berikut menunjukkan penggunaan konstruktor nilai tabel sebagai tabel turunan. Ini memungkinkan untuk menyisipkan lebih dari 1000 baris dari satu konstruktor nilai tabel.

CREATE TABLE dbo.Test ([Value] INT);  
  
INSERT INTO dbo.Test ([Value])  
  SELECT drvd.[NewVal]
  FROM   (VALUES (0), (1), (2), (3), ..., (5000)) drvd([NewVal]);

Lihat Juga

INSERT (Transact-SQL)
MERGE (Transact-SQL)
FROM (Transact-SQL)

Bagikan melalui