Bagikan melalui

Konstruktor Nilai Tabel (Transact-SQL)

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceDatabase SQL di Microsoft Fabric

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 baik sebagai VALUES klausul INSERT ... VALUES pernyataan, atau sebagai tabel turunan USING dalam MERGE klausa pernyataan atau FROM klausa.

Konvensi sintaks transact-SQL

Syntax

VALUES ( <row value expression list> ) [ ,...n ]   

<row value expression list> ::=  
    {<row value expression> } [ ,...n ]  

<row value expression> ::=  
    { DEFAULT | NULL | expression }

Arguments

VALUES

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 disisipkan. DEFAULT tidak valid untuk kolom identitas. Ketika ditentukan dalam konstruktor nilai tabel, DEFAULT hanya diperbolehkan dalam INSERT pernyataan.

expression

Konstanta, variabel, atau ekspresi. Ekspresi tidak boleh berisi EXECUTE pernyataan.

Limitations

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

Saat digunakan sebagai VALUES klausul INSERT ... VALUES pernyataan, ada batas 1.000 baris. Kesalahan 10738 dikembalikan jika jumlah baris melebihi maksimum. Untuk menyisipkan lebih dari 1.000 baris, gunakan salah satu metode berikut:

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 multi-baris INSERT mengikuti properti konversi jenis data sintaksis UNION ALL . Ini menghasilkan konversi implisit dari jenis yang tidak cocok ke jenis jenis data 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

INSERT Ketika pernyataan dijalankan, SQL Server mencoba mengonversi 'a' menjadi bilangan bulat karena prioritas jenis 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));

Examples

A. 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. Sisipkan 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. Tentukan beberapa nilai sebagai tabel turunan dalam klausa FROM

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

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. Tentukan 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. Sisipkan lebih dari 1.000 baris

Contoh berikut menunjukkan penggunaan konstruktor nilai tabel sebagai tabel turunan. Ini memungkinkan untuk menyisipkan lebih dari 1.000 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]);

Konten terkait

  • Last updated on