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

Статья
03/07/2014

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

Применимо для следующих объектов: SQL Server (SQL Server 2008 по текущую версию), База данных SQL Windows Azure (С первоначального выпуска по текущий выпуск).

Значок ссылки на раздел Cинтаксические обозначения в 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.

Ограничения

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

Создайте несколько инструкций INSERT
Используйте производную таблицу
Выполните массовый импорт данных с помощью программы bcp или инструкции BULK INSERT

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

USE AdventureWorks2012;
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 AdventureWorks2012;
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 AdventureWorks2012;
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 AdventureWorks2012;
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)

Поделиться через