Konstruktor Nilai Tabel (Transact-SQL)
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.
Sintaksis
VALUES ( <row value expression list> ) [ ,...n ]
<row value expression list> ::=
{<row value expression> } [ ,...n ]
<row value expression> ::=
{ DEFAULT | NULL | expression }
Catatan
Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.
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)