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

Указывает набор выражений значений строк, из которых создается таблица. Конструктор табличных значений Transact-SQL позволяет указывать несколько строк данных в одной инструкции DML. Конструктор табличных значений может указываться в предложении VALUES инструкции INSERT, в предложении 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 }

Аргументы

• VALUES
  Содержит списки выражений значений строк. Каждый список должен заключаться в скобки и разделяться запятыми.

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

• DEFAULT
  Указывает компоненту Database Engine выполнять принудительную вставку значения по умолчанию, определенного для столбца. Если для столбца не задано значение по умолчанию и он может содержать значение NULL, вставляется значение NULL. Значение DEFAULT недопустимо для столбца идентификаторов. Указание DEFAULT в конструкторе табличных значений допускается только в инструкции INSERT.

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

Ограничения

Максимальное количество строк, которое можно построить с помощью конструктора табличных значений, составляет 1000. Чтобы вставить более 1000 строк, создайте несколько инструкций INSERT или выполните массовый импорт данных с помощью программы bcp или инструкции BULK INSERT.

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

USE AdventureWorks;
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 AdventureWorks;
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 AdventureWorks;
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

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

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

USE AdventureWorks;
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;

См. также

Справочник

Инструкция INSERT (Transact-SQL)

MERGE (Transact-SQL)

Предложение FROM (Transact-SQL)