Статья
09/28/2010

Примеры использования инструкции INSERT (Transact-SQL)

В этом разделе приведены примеры применения инструкции INSERT. Примеры сгруппированы по следующим категориям.

Категория	Используемые элементы синтаксиса
Базовый синтаксис	INSERT • конструктор табличных значений
Обработка значений столбцов	IDENTITY • NEWID • значения по умолчанию • определяемые пользователем типы
Вставка данных из других таблиц	INSERT…SELECT • INSERT…EXECUTE • WITH обобщенное табличное выражение • TOP
Указание целевых объектов, не являющихся стандартными таблицами	Представления • табличные переменные
Вставка строк в удаленную таблицу	Связанный сервер • функция набора строк OPENQUERY • функция набора строк OPENDATASOURCE
Массовая загрузка данных из таблиц или файлов данных	INSERT…SELECT • функция OPENROWSET
Переопределение стандартных действий, выполняемых оптимизатором запросов, с помощью подсказок	Табличные подсказки
Сбор результатов инструкции INSERT	Предложение OUTPUT

Базовый синтаксис

В примерах из этого раздела показаны основные функции инструкции INSERT и используются минимально необходимый синтаксис.

А. Вставка одной строки данных

В следующем примере в таблицу Production.UnitMeasure вставляется одна строка. Столбцы этой таблицы имеют имена UnitMeasureCode, Name и ModifiedDate. Так как значения для всех столбцов предоставлены и перечислены в том же порядке, что и столбцы в таблице, то в списке столбцов не нужно указывать имена столбцов.

USE AdventureWorks;
GO
INSERT INTO Production.UnitMeasure
VALUES (N'FT', N'Feet', '20080414');
GO

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

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

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

В. Вставка данных в порядке, отличном от порядка столбцов таблицы

В следующем примере используется список столбцов для явного указания значений, которые будут вставляться в каждый столбец. В таблице Production.UnitMeasure вначале идет столбец UnitMeasureCode, затем Name и ModifiedDate; однако столбцы в column_list перечислены в другом порядке.

USE AdventureWorks;
GO
INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode,
    ModifiedDate)
VALUES (N'Square Yards', N'Y2', GETDATE());
GO

Обработка значений столбцов

В примерах из этого раздела показаны методы вставки значений в столбцы, определенные со свойством IDENTITY, свойством DEFAULT или с такими типами данных как uniqueidentifer, либо в столбцы определяемых пользователем типов.

А. Вставка данных в таблицу со столбцами, имеющими значения по умолчанию

В следующем примере показана вставка строк в таблицу со столбцами, которые автоматически формируют значение или имеют значение по умолчанию. Столбец Column_1 является вычисляемым и автоматически формирует значение путем сцепления строки со значением, вставляемым в столбец column_2. Столбец Column_2 определяется с ограничением по умолчанию. Если для этого столбца не указано значение, то используется значение по умолчанию. Столбец Column_3 определяется с типом данных rowversion и автоматически формирует уникальное увеличивающееся двоичное число. Столбец Column_4 не формирует значение автоматически. Если для этого столбца не указывается значение, вставляется значение NULL. Инструкции INSERT вставляют строки, которые содержат значения для некоторых столбцов, но не для всех. В последней инструкции INSERT столбцы не указываются и вставляются только значения по умолчанию с помощью предложения DEFAULT VALUES.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 
(
    column_1 int IDENTITY, 
    column_2 varchar(30) 
        CONSTRAINT default_name DEFAULT ('my column default'),
    column_3 timestamp,
    column_4 varchar(40) NULL
);
GO
INSERT INTO dbo.T1 (column_4) 
    VALUES ('Explicit value');
INSERT INTO dbo.T1 (column_2, column_4) 
    VALUES ('Explicit value', 'Explicit value');
INSERT INTO dbo.T1 (column_2) 
    VALUES ('Explicit value');
INSERT INTO T1 DEFAULT VALUES; 
GO
SELECT column_1, column_2, column_3, column_4
FROM dbo.T1;
GO

Б. Вставка данных в таблицу со столбцом идентификаторов

В следующем примере показаны различные методы вставки данных в столбец идентификаторов. Первые две инструкции INSERT позволяют создать значения идентификаторов для новых строк. Третья инструкция INSERT переопределяет свойство IDENTITY столбца с помощью инструкции SET IDENTITY_INSERT и вставляет явно заданное значение в столбец идентификаторов.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 ( column_1 int IDENTITY, column_2 VARCHAR(30));
GO
INSERT T1 VALUES ('Row #1');
INSERT T1 (column_2) VALUES ('Row #2');
GO
SET IDENTITY_INSERT T1 ON;
GO
INSERT INTO T1 (column_1,column_2) 
    VALUES (-99, 'Explicit identity value');
GO
SELECT column_1, column_2
FROM T1;
GO

В. Вставка данных в столбец uniqueidentifier с помощью функции NEWID()

В следующем примере с помощью функции NEWID() получается идентификатор GUID для столбца column_2. В отличие от столбцов идентификаторов, компонент Database Engine не создает значения для столбцов с типом данных uniqueidentifier автоматически, как показано во второй инструкции INSERT.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 
(
    column_1 int IDENTITY, 
    column_2 uniqueidentifier,
);
GO
INSERT INTO dbo.T1 (column_2) 
    VALUES (NEWID());
INSERT INTO T1 DEFAULT VALUES; 
GO
SELECT column_1, column_2
FROM dbo.T1;
GO

Г. Вставка данных в столбцы определяемого пользователем типа

В следующих инструкциях Transact-SQL вставляются три строки в столбец PointValue таблицы Points. Этот столбец использует Определяемый пользователем тип CLR. Тип данных Point состоит из целочисленных значений X и Y, которые представлены как свойства определяемого пользователем типа. Необходимо использовать функцию CAST или CONVERT, чтобы привести разделенные запятой значения X и Y к типу Point. Первые две инструкции используют функцию CONVERT, чтобы преобразовать строковое значение к типу Point, а третья инструкция использует функцию CAST. Дополнительные сведения см. в разделе Работа с данными определяемого пользователем типа.

INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '3,4'));
INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '1,5'));
INSERT INTO dbo.Points (PointValue) VALUES (CAST ('1,99' AS Point));

Вставка данных из других таблиц

В примерах из этого раздела показаны методы вставки строк из одной таблицы в другую.

А. Вставка данных из других таблиц с помощью параметров SELECT и EXECUTE

В следующем примере показано, как вставлять данные из одной таблицы в другую с помощью INSERT…SELECT или INSERT…EXECUTE. Каждый метод основан на многотабличной инструкции SELECT, содержащей выражение и литеральное значение в списке столбцов.

В первой инструкции INSERT используется инструкция SELECT для получения данных из исходных таблиц (Employee, SalesPerson и Contact) и сохранения результирующего набора в таблице EmployeeSales. Во второй инструкции INSERT используется предложение EXECUTE для вызова хранимой процедуры, содержащей инструкцию SELECT, а в третьей инструкции INSERT используется предложение EXECUTE для ссылки на инструкцию SELECT в виде строкового литерала.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO
IF OBJECT_ID ('dbo.uspGetEmployeeSales', 'P') IS NOT NULL
    DROP PROCEDURE uspGetEmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( DataSource   varchar(20) NOT NULL,
  EmployeeID   varchar(11) NOT NULL,
  LastName     varchar(40) NOT NULL,
  SalesDollars money NOT NULL
);
GO
CREATE PROCEDURE dbo.uspGetEmployeeSales 
AS 
    SET NOCOUNT ON;
    SELECT 'PROCEDURE', e.EmployeeID, c.LastName, 
        sp.SalesYTD 
    FROM HumanResources.Employee AS e 
        INNER JOIN Sales.SalesPerson AS sp  
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE '2%'
    ORDER BY e.EmployeeID, c.LastName;
GO
--INSERT...SELECT example
INSERT dbo.EmployeeSales
    SELECT 'SELECT', e.EmployeeID, c.LastName, sp.SalesYTD 
    FROM HumanResources.Employee AS e
        INNER JOIN Sales.SalesPerson AS sp
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE '2%'
    ORDER BY e.EmployeeID, c.LastName;
GO
--INSERT...EXECUTE procedure example
INSERT EmployeeSales 
EXECUTE uspGetEmployeeSales;
GO
--INSERT...EXECUTE('string') example
INSERT EmployeeSales 
EXECUTE 
('
SELECT ''EXEC STRING'', e.EmployeeID, c.LastName, 
    sp.SalesYTD 
    FROM HumanResources.Employee AS e 
        INNER JOIN Sales.SalesPerson AS sp 
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE ''2%''
    ORDER BY e.EmployeeID, c.LastName
');
GO
--Show results.
SELECT DataSource,EmployeeID,LastName,SalesDollars
FROM dbo.EmployeeSales;
GO

Б. Определение вставляемых данных с помощью обобщенного табличного выражения WITH

В следующем примере создается таблица NewEmployee. Обобщенное табличное выражение (EmployeeTemp) определяет строки из одной или нескольких таблиц для вставки в таблицу NewEmployee. Инструкция INSERT ссылается на столбцы в обобщенном табличном выражении.

USE AdventureWorks;
GO
IF OBJECT_ID (N'HumanResources.NewEmployee', N'U') IS NOT NULL
    DROP TABLE HumanResources.NewEmployee;
GO
CREATE TABLE HumanResources.NewEmployee
(
    EmployeeID int NOT NULL,
    LastName nvarchar(50) NOT NULL,
    FirstName nvarchar(50) NOT NULL,
    Phone Phone NULL,
    AddressLine1 nvarchar(60) NOT NULL,
    City nvarchar(30) NOT NULL,
    State nchar(3) NOT NULL, 
    PostalCode nvarchar(15) NOT NULL,
    CurrentFlag Flag
);
GO
WITH EmployeeTemp (EmpID, LastName, FirstName, Phone, 
                   Address, City, StateProvince, 
                   PostalCode, CurrentFlag)
AS (SELECT 
        e.EmployeeID, c.LastName, c.FirstName, c.Phone,
        a.AddressLine1, a.City, sp.StateProvinceCode, 
        a.PostalCode, e.CurrentFlag
    FROM HumanResources.Employee e
        INNER JOIN HumanResources.EmployeeAddress AS ea
        ON e.EmployeeID = ea.EmployeeID
        INNER JOIN Person.Address AS a
        ON ea.AddressID = a.AddressID
        INNER JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
        INNER JOIN Person.Contact as c
        ON e.ContactID = c.ContactID
    )
INSERT INTO HumanResources.NewEmployee 
    SELECT EmpID, LastName, FirstName, Phone, 
           Address, City, StateProvince, PostalCode, CurrentFlag
    FROM EmployeeTemp;
GO

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

В следующем примере используется предложение TOP для ограничения числа строк, вставляемых в таблицу NewEmployee из таблицы Employee. В примере вставляются данные адресов для случайного набора из 10 сотрудников из таблицы Employee. Затем выполняется инструкция SELECT, чтобы проверить содержимое таблицы NewEmployee.

USE AdventureWorks;
GO
IF OBJECT_ID (N'HumanResources.NewEmployee', N'U') IS NOT NULL
    DROP TABLE HumanResources.NewEmployee;
GO
CREATE TABLE HumanResources.NewEmployee
(
    EmployeeID int NOT NULL,
    LastName nvarchar(50) NOT NULL,
    FirstName nvarchar(50) NOT NULL,
    Phone Phone NULL,
    AddressLine1 nvarchar(60) NOT NULL,
    City nvarchar(30) NOT NULL,
    State nchar(3) NOT NULL, 
    PostalCode nvarchar(15) NOT NULL,
    CurrentFlag Flag
);
GO
INSERT TOP (10) INTO HumanResources.NewEmployee 
    SELECT
       e.EmployeeID, c.LastName, c.FirstName, c.Phone,
       a.AddressLine1, a.City, sp.StateProvinceCode, 
       a.PostalCode, e.CurrentFlag
    FROM HumanResources.Employee e
        INNER JOIN HumanResources.EmployeeAddress AS ea
        ON e.EmployeeID = ea.EmployeeID
        INNER JOIN Person.Address AS a
        ON ea.AddressID = a.AddressID
        INNER JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
        INNER JOIN Person.Contact as c
        ON e.ContactID = c.ContactID;
GO
SELECT  EmployeeID, LastName, FirstName, Phone,
        AddressLine1, City, State, PostalCode, CurrentFlag
FROM HumanResources.NewEmployee;
GO

Указание целевых объектов, не являющихся стандартными таблицами

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

А. Вставка данных путем указания представления

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

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
IF OBJECT_ID ('dbo.V1', 'V') IS NOT NULL
    DROP VIEW dbo.V1;
GO
CREATE TABLE T1 ( column_1 int, column_2 varchar(30));
GO
CREATE VIEW V1 AS 
SELECT column_2, column_1 
FROM T1;
GO
INSERT INTO V1 
    VALUES ('Row 1',1);
GO
SELECT column_1, column_2 
FROM T1;
GO
SELECT column_1, column_2
FROM V1;
GO

Б. Вставка данных в табличную переменную

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

USE AdventureWorks;
GO
-- Create the table variable.
DECLARE @MyTableVar table(
    LocationID int NOT NULL,
    CostRate smallmoney NOT NULL,
    NewCostRate AS CostRate * 1.5,
    ModifiedDate datetime);

-- Insert values into the table variable.
INSERT INTO @MyTableVar (LocationID, CostRate, ModifiedDate)
    SELECT LocationID, CostRate, GETDATE() FROM Production.Location
    WHERE CostRate > 0;

-- View the table variable result set.
SELECT * FROM @MyTableVar;
GO

Вставка строк в удаленную таблицу

В примерах из этого раздела показано, как вставлять строки в удаленную целевую таблицу с помощью связанного сервера или функции набора строк, ссылающихся на удаленную таблицу.

А. Вставка данных в удаленную таблицу с помощью связанного сервера

В следующем примере вставляются строки в удаленную таблицу. Сначала в примере создается ссылка на удаленный источник данных с помощью процедуры sp_addlinkedserver. Затем указывается имя связанного сервера MyLinkServer в составе четырехкомпонентного имени в формате сервер.каталог.схема.объект.

USE master;
GO
-- Create a link to the remote data source. 
-- Specify a valid server name for @datasrc as 'server_name' or 'server_name\instance_name'.

EXEC sp_addlinkedserver @server = N'MyLinkServer',
    @srvproduct = N' ',
    @provider = N'SQLNCLI', 
    @datasrc = N'server_name',
    @catalog = N'AdventureWorks';
GO

USE AdventureWorks;
GO
-- Specify the remote data source in the FROM clause using a four-part name 
-- in the form linked_server.catalog.schema.object.

INSERT INTO MyLinkServer.AdventureWorks.HumanResources.Department (Name, GroupName)
VALUES (N'Public Relations', N'Executive General and Administration');
GO

Б. Вставка данных в удаленную таблицу с помощью функции OPENQUERY

В следующем примере вставляется строка в удаленную таблицу путем указания функции набора строк OPENQUERY. В этом примере используется имя связанного сервера, созданного в предыдущем примере.

-- Use the OPENQUERY function to access the remote data source.

INSERT OPENQUERY (MyLinkServer, 'SELECT Name, GroupName FROM AdventureWorks.HumanResources.Department')
VALUES ('Environmental Impact', 'Engineering');
GO

В. Вставка данных в удаленную таблицу с помощью функции OPENDATASOURCE

В следующем примере вставляется строка в удаленную таблицу путем указания функции набора строк OPENDATASOURCE. Укажите допустимое имя сервера для источника данных в формате имя_сервера или имя_сервера\имя_экземпляра.

-- Use the OPENDATASOURCE function to specify the remote data source.
-- Specify a valid server name for Data Source using the format server_name or server_name\instance_name.

INSERT INTO OPENDATASOURCE('SQLNCLI',
    'Data Source= <server_name>; Integrated Security=SSPI')
    .AdventureWorks.HumanResources.Department (Name, GroupName)
    VALUES (N'Standards and Methods', 'Quality Assurance');
GO

Массовая загрузка данных из таблиц или файлов данных

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

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

В следующем примере создается новая таблица (куча), в которую вставляются данные из другой таблицы с минимальным протоколированием. В примере предполагается, что для базы данных AdventureWorks выбрана модель восстановления FULL. Чтобы убедиться, что применяется минимальное протоколирование, модель восстановления базы данных AdventureWorks устанавливается в значение BULK_LOGGED перед вставкой строк и возвращается в значение FULL после выполнения инструкции INSERT INTO…SELECT. Кроме того, для целевой таблицы Sales.SalesHistory указывается подсказка TABLOCK. Это обеспечивает минимальное использование журнала транзакций инструкцией и ее эффективное выполнение.

USE AdventureWorks;
GO
-- Create the target heap.
CREATE TABLE Sales.SalesHistory(
    SalesOrderID int NOT NULL,
    SalesOrderDetailID int NOT NULL,
    CarrierTrackingNumber nvarchar(25) NULL,
    OrderQty smallint NOT NULL,
    ProductID int NOT NULL,
    SpecialOfferID int NOT NULL,
    UnitPrice money NOT NULL,
    UnitPriceDiscount money NOT NULL,
    LineTotal money NOT NULL,
    rowguid uniqueidentifier ROWGUIDCOL  NOT NULL,
    ModifiedDate datetime NOT NULL );
GO
-- Temporarily set the recovery model to BULK_LOGGED.
ALTER DATABASE AdventureWorks
SET RECOVERY BULK_LOGGED;
GO
-- Transfer data from Sales.SalesOrderDetail to Sales.SalesHistory
INSERT INTO Sales.SalesHistory WITH (TABLOCK)
    (SalesOrderID, 
     SalesOrderDetailID,
     CarrierTrackingNumber, 
     OrderQty, 
     ProductID, 
     SpecialOfferID, 
     UnitPrice, 
     UnitPriceDiscount,
     LineTotal, 
     rowguid, 
     ModifiedDate)
SELECT * FROM Sales.SalesOrderDetail;
GO
-- Reset the recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;

Б. Использование функции OPENROWSET с параметром BULK для массовой загрузки данных в таблицу

В следующем примере в таблицу вставляются строки из файла данных путем указания функции OPENROWSET. Указывается табличная подсказка IGNORE_TRIGGERS для оптимизации производительности. Дополнительные примеры см. в разделе Массовый импорт данных при помощи инструкции BULK INSERT или OPENROWSET(BULK...).

-- Use the OPENROWSET function to specify the data source and specifies the IGNORE_TRIGGERS table hint.

INSERT INTO HumanResources.Department WITH (IGNORE_TRIGGERS) (Name, GroupName)
SELECT b.Name, b.GroupName 
FROM OPENROWSET (
    BULK 'C:\SQLFiles\DepartmentData.txt',
    FORMATFILE = 'C:\SQLFiles\BulkloadFormatFile.xml',
    ROWS_PER_BATCH = 15000)AS b ;
GO

Переопределение стандартных действий, выполняемых оптимизатором запросов, с помощью подсказок

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

Внимание!
Поскольку оптимизатор запросов SQL Server обычно выбирает наилучший план выполнения запроса, подсказки рекомендуется использовать только опытным разработчикам и администраторам баз данных в качестве последнего средства.

Поскольку оптимизатор запросов SQL Server обычно выбирает наилучший план выполнения запроса, подсказки рекомендуется использовать только опытным разработчикам и администраторам баз данных в качестве последнего средства.

А. Использование подсказки TABLOCK для указания метода блокировки

В следующем примере указывается, что на таблицу Production.Location накладывается монопольная блокировка (X), которая удерживается до завершения инструкции INSERT.

USE AdventureWorks;
GO
INSERT INTO Production.Location WITH (XLOCK)
(Name, CostRate, Availability)
VALUES ( N'Final Inventory', 15.00, 80.00);
GO

Сбор результатов инструкции INSERT

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

А. Использование предложения OUTPUT с инструкцией INSERT

В следующем примере вставляется строка в таблицу ScrapReason и с помощью предложения OUTPUT результаты инструкции возвращаются в табличной переменной @MyTableVar. Так как столбец ScrapReasonID определен с помощью свойства IDENTITY, то значение для этого столбца не указывается в инструкции INSERT. Однако следует заметить, что значение, созданное компонентом Database Engine для этого столбца, возвращается в предложении OUTPUT в столбце INSERTED.ScrapReasonID.

USE AdventureWorks;
GO
DECLARE @MyTableVar table( ScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate 
FROM Production.ScrapReason;
GO

Б. Применение предложения OUTPUT со столбцами идентификаторов и вычисляемыми столбцами

В следующем примере создается таблица EmployeeSales, а затем в нее с помощью инструкции INSERT вставляется несколько строк, получаемых инструкцией SELECT из исходных таблиц. Таблица EmployeeSales содержит столбец идентификаторов (EmployeeID) и вычисляемый столбец (ProjectedSales). Поскольку эти значения создаются компонентом Database Engine при вставке, ни один из этих столбцов нельзя задавать в @MyTableVar.

USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID   int IDENTITY (1,5)NOT NULL,
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL,
  ProjectedSales AS CurrentSales * 1.10 
);
GO
DECLARE @MyTableVar table(
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  CurrentSales money NOT NULL
  );

INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)
  OUTPUT INSERTED.LastName, 
         INSERTED.FirstName, 
         INSERTED.CurrentSales
  INTO @MyTableVar
    SELECT c.LastName, c.FirstName, sp.SalesYTD
    FROM HumanResources.Employee AS e
        INNER JOIN Sales.SalesPerson AS sp
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE '2%'
    ORDER BY c.LastName, c.FirstName;

SELECT LastName, FirstName, CurrentSales
FROM @MyTableVar;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO

В. Вставка данных, возвращенных предложением OUTPUT

В следующем примере производится отслеживание данных, возвращаемых предложением OUTPUT инструкции MERGE, а затем производится вставка этих данных в другую таблицу. Инструкция MERGE ежедневно обновляет столбец Quantity таблицы ProductInventory в соответствии с заказами, обрабатываемыми в таблице SalesOrderDetail. Она также удаляет строки для продуктов с нулевыми остатками. В примере удаленные столбцы отслеживаются и вставляются в другую таблицу, ZeroInventory, которая содержит продукты, по которым нет запасов.

USE AdventureWorks;
GO
IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL
    DROP TABLE Production.ZeroInventory;
GO
--Create ZeroInventory table.
CREATE TABLE Production.ZeroInventory (ProductID int);
GO

INSERT INTO Production.ZeroInventory (ProductID)
SELECT ProductID
FROM
(   MERGE Production.ProductInventory AS pi
    USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
           JOIN Sales.SalesOrderHeader AS soh
           ON sod.SalesOrderID = soh.SalesOrderID
           AND soh.OrderDate = '20030401'
           GROUP BY ProductID) AS src (ProductID, OrderQty)
    ON (pi.ProductID = src.ProductID)
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
        THEN DELETE
    WHEN MATCHED
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    OUTPUT $action, deleted.ProductID) AS Changes (Action, ProductID)
WHERE Action = 'DELETE';
GO
SELECT ProductID FROM Production.ZeroInventory;

См. также

Справочник

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

IDENTITY (свойство) (Transact-SQL)

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