Конструктор табличных значений (Transact-SQL)

2025-06-19

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azureбазе данных SQL в Microsoft Fabric

Задает набор выражений значений строк, которые будут использоваться для создания таблицы. Конструктор табличных значений Transact-SQL позволяет указать в одной инструкции DML несколько строк данных. Конструктор значений таблицы можно указать как предложение инструкции, так VALUES и в виде производной таблицы в INSERT ... VALUES предложении инструкции USING или MERGE предложения.FROM

Соглашения о синтаксисе Transact-SQL

Синтаксис

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

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

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

Аргументы

ЗНАЧЕНИЯ

Представляет списки выражений значений строк. Все списки должны быть заключены в круглые скобки и разделены запятыми.

Количество значений в каждом списке должно быть одинаковым, а значения должны следовать в том же порядке, что и столбцы таблицы. Должно быть указано значение для всех столбцов в таблице, либо список столбцов должен явно указывать столбцы для всех входных значений.

ПО УМОЛЧАНИЮ

Указывает, что ядро СУБД должно вставлять значения по умолчанию, определенные для столбца. Если значение по умолчанию не существует для столбца, а столбец разрешает значения NULL, NULL вставляется. DEFAULT недопустимо для столбца удостоверений. При указании в конструкторе DEFAULT табличного значения допускается только в инструкции INSERT .

выражение

Константой, переменной или выражением. Выражение не может содержать инструкцию EXECUTE .

Ограничения

При использовании в виде производной таблицы ограничение на количество строк отсутствует.

При использовании в качестве VALUES предложения инструкции INSERT ... VALUES имеется ограничение в 1000 строк. Если число строк превышает 1000, возвращается ошибка 10738. Чтобы вставить более 1000 строк, используйте один из следующих методов:

Создание нескольких INSERT операторов.
Используйте производную таблицу.
Массовый импорт данных с помощью служебной программы bcp, класса .NET SqlBulkCopy, OPENROWSET BULK или инструкции BULK INSERT .

Для выражения значения строк можно использовать только отдельные скалярные значения. Вложенный запрос, содержащий несколько столбцов, не может быть использован в выражении значений строк. Например, следующий код вызовет ошибку синтаксиса, поскольку в третьем списке выражений значений строк содержится вложенный запрос с несколькими столбцами.

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

Однако можно переписать инструкцию таким образом, чтобы каждый столбец отдельно задавался во вложенном запросе. В следующем примере в таблицу MyProducts успешно вставляются три строки.

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

Типы данных

Значения, указанные в инструкции с несколькими строками INSERT , соответствуют свойствам преобразования типов данных синтаксиса UNION ALL . Это приводит к неявному преобразованию несовпаденных типов в тип приоритета более высокого типа данных. Если неявное преобразование не поддерживается, возвращается ошибка. Например, следующая инструкция вставляет целочисленное значение и символьное значение в столбец типа char.

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

INSERT При выполнении инструкции SQL Server пытается преобразовать "a" в целое число, так как приоритет типа данных указывает, что целое число имеет более высокий тип, чем символ. Попытка преобразования оканчивается неудачей и возвращается ошибка. Этой ошибки можно избежать путем явного преобразования значений при необходимости. Например, приведенную выше инструкцию можно записать следующим образом:

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

Примеры

А. Вставка нескольких строк данных

В следующем примере создается таблица dbo.Departments, а затем при помощи конструктора табличных значений в таблицу вставляется пять строк. Так как значения для всех столбцов предоставлены и перечислены в том же порядке, что и столбцы в таблице, то не нужно в параметре указывать имена столбцов.

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

В. Вставка нескольких строк со значениями DEFAULT и NULL

В следующем примере показано указание DEFAULT и NULL использование конструктора значений таблицы для вставки строк в таблицу.

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;

В. Указание нескольких значений в виде производной таблицы в предложении FROM

В следующих примерах конструктор значений таблицы используется для указания нескольких значений в FROM предложении инструкции 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;

Д. Указание нескольких значений в качестве производной исходной таблицы в инструкции MERGE

В следующем примере используется MERGE для изменения SalesReason таблицы путем обновления или вставки строк. Если значение исходной таблицы совпадает со значением NewName в Name столбце целевой таблицы (SalesReason), ReasonType столбец обновляется в целевой таблице. Если значение NewName не совпадает со значением в целевой таблице, исходная строка вставляется в целевую таблицу. В качестве исходной используется производная таблица, в которой конструктор табличных значений Transact-SQL используется для указания нескольких строк исходной таблицы.

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;

Е. Вставка более 1000 строк

Следующий пример демонстрирует использование конструктора табличных значений в виде производной таблицы. Это позволяет вставлять более 1000 строк из одного конструктора значений таблицы.

CREATE TABLE dbo.Test ([Value] INT);  

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