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

Статья
05/19/2015

Добавляет одну или несколько строк в таблицу или представление SQL Server 2012. Примеры см. в разделе Примеры.

Значок ссылки на раздел Синтаксические обозначения в Transact-SQL

Синтаксис

[ WITH <common_table_expression> [ ,...n ] ]
INSERT 
{
        [ TOP ( expression ) [ PERCENT ] ] 
        [ INTO ] 
        { <object> | rowset_function_limited 
          [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
        }
    {
        [ ( column_list ) ] 
        [ <OUTPUT Clause> ]
        { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n     ] 
        | derived_table 
        | execute_statement
        | <dml_table_source>
        | DEFAULT VALUES 
        }
    }
}
[;]

<object> ::=
{ 
    [ server_name . database_name . schema_name . 
      | database_name .[ schema_name ] . 
      | schema_name . 
    ]
  table_or_view_name
}

<dml_table_source> ::=
    SELECT <select_list>
    FROM ( <dml_statement_with_output_clause> ) 
      [AS] table_alias [ ( column_alias [ ,...n ] ) ]
    [ WHERE <search_condition> ]
        [ OPTION ( <query_hint> [ ,...n ] ) ]

<column_definition> ::=
 column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

<data type> ::= 
[ type_schema_name . ] type_name 
    [ ( precision [ , scale ] | max ]

-- External tool only syntax
INSERT 
{
    [BULK]
    [ database_name . [ schema_name ] . | schema_name . ]
    [ table_name | view_name ]
    ( <column_definition> )
    [ WITH (
        [ [ , ] CHECK_CONSTRAINTS ]
        [ [ , ] FIRE_TRIGGERS ]
        [ [ , ] KEEP_NULLS ]
        [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
        [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
        [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
        [ [ , ] TABLOCK ]
    ) ]
}
[; ]

Аргументы

WITH <common_table_expression>
Определяет временный именованный результирующий набор, также называемый обобщенным табличным выражением, определенным в области инструкции INSERT. Результирующий набор получается из инструкции SELECT. Дополнительные сведения см. в разделе WITH обобщенное_табличное_выражение (Transact-SQL).
TOP (expression) [ PERCENT ]
Задает число или процент вставляемых случайных строк. expression может быть либо числом, либо процентом от числа строк. Дополнительные сведения см. в разделе TOP (Transact-SQL).
INTO
Необязательное ключевое слово, которое можно использовать между ключевым словом INSERT и целевой таблицей.
server_name
Имя связанного сервера, на котором расположены таблица или представление. server_name может указываться как имя связанного сервера или с помощью функции OPENDATASOURCE.

Если аргумент server_name задается в виде связанного сервера, то необходимы аргументы database_name и schema_name. Если аргумент server_name задается с помощью функции OPENDATASOURCE, то аргументы database_name и schema_name могут применяться не ко всем источникам данных, в зависимости от возможностей поставщика OLE DB, который обращается к удаленному объекту.
database_name
Имя базы данных.
schema_name
Имя схемы, к которой принадлежит таблица или представление.
table_or view_name
Имя таблицы или представления, которые принимают данные.

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

Представление, на которое ссылается аргумент table_or_view_name, должно быть обновляемым и ссылаться только на одну базовую таблицу в предложении FROM данного представления. Например, инструкция INSERT в многотабличном представлении должна использовать аргумент column_list, который ссылается только на столбцы из одной базовой таблицы. Дополнительные сведения об обновляемых представлениях см. в разделе CREATE VIEW (Transact-SQL).
rowset_function_limited
Либо функция OPENQUERY, либо функция OPENROWSET. Использование этих функций зависит от возможностей поставщика OLE DB, который обращается к удаленному объекту.

WITH ( <table_hint_limited> [... n ] )
Задает одно или несколько табличных указаний, разрешенных для целевой таблицы. Необходимо использовать ключевое слово WITH и круглые скобки.

Нельзя использовать подсказки READPAST, NOLOCK, и READUNCOMMITTED. Дополнительные сведения о табличных подсказках см. в разделе Табличные указания (Transact-SQL).

Важно!
Возможность указать подсказки HOLDLOCK, SERIALIZABLE, READCOMMITTED, REPEATABLEREAD или UPDLOCK в целевых таблицах инструкций INSERT будет удалена в будущих версиях SQL Server. Эти указания не влияют на производительность инструкций INSERT. Избегайте применять их в новых разработках и запланируйте внесение изменений в приложения, использующие их в настоящее время.

Возможность указать подсказки HOLDLOCK, SERIALIZABLE, READCOMMITTED, REPEATABLEREAD или UPDLOCK в целевых таблицах инструкций INSERT будет удалена в будущих версиях SQL Server. Эти указания не влияют на производительность инструкций INSERT. Избегайте применять их в новых разработках и запланируйте внесение изменений в приложения, использующие их в настоящее время.

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

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

Если столбец не внесен в column_list, то компонент Ядро СУБД должен обеспечить значение, основанное на определении столбца; в противном случае строку нельзя будет загрузить. Компонент Ядро СУБД автоматически задает значение для столбца, если столбец имеет следующие характеристики.
- Имеется свойство IDENTITY. Используется следующее значение приращения для идентификатора.
- Имеется стандартное значение. Используется стандартное значение для столбца.
- Имеется тип данных timestamp. В этом случае используется текущее значение отметки времени.
- Допускаются значения NULL. Используется значение NULL.
- Вычисляемый столбец. Используется вычисленное значение.
Аргумент column_list необходимо использовать, если в столбец идентификаторов вставляются явно заданные значения, а параметру SET IDENTITY_INSERT необходимо присвоить значение ON для таблицы.
Предложение OUTPUT
Возвращает вставленные строки во время операции вставки. Результаты могут возвращаться в обрабатывающее приложение или вставляться в таблицу или табличную переменную для дальнейшей обработки.

Предложение OUTPUT не поддерживается инструкциями DML, которые ссылаются на локальные секционированные представления, распределенные секционированные представления, расположенные удаленно таблицы или инструкции INSERT, содержащие аргумент execute_statement. Предложение OUTPUT INTO не поддерживается в инструкциях INSERT, содержащих предложение <dml_table_source>.
VALUES
Позволяет использовать один или несколько списков вставляемых значений данных. Для каждого столбца в column_list, если этот параметр указан или присутствует в таблице, должно быть одно значение. Список значений должен быть заключен в скобки.

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

Можно использовать конструктор строк Transact-SQL (также называемый конструктором табличных значений), позволяющий указать несколько строк в одной инструкции INSERT. Этот конструктор строк состоит из одного предложения VALUES со списками из нескольких значений, заключенными в круглые скобки и разделенными запятыми. Дополнительные сведения см. в разделе Конструктор табличных значений (Transact-SQL).
DEFAULT
Указывает компоненту Ядро СУБД необходимость принудительно загружать значения по умолчанию, определенные для столбца. Если для столбца не задано значение по умолчанию и он может содержать значение NULL, вставляется значение NULL. В столбцы с типом данных timestamp вставляется следующее значение отметки времени. Значение DEFAULT недопустимо для столбца идентификаторов.
expression
Константа, переменная или выражение. Выражение не может содержать инструкцию EXECUTE.

При ссылке на типы данных символов Юникода nchar, nvarchar и ntext выражение expression должно начинаться с заглавной буквы «N». Если префикс «N» не указан, SQL Server выполнит преобразование строки в кодовую страницу, соответствующую параметрам сортировки базы данных или столбца, действующим по умолчанию. Любые символы, не входящие в эту кодовую страницу, будут утрачены.
derived_table
Любая допустимая инструкция SELECT, возвращающая строки данных, которые загружаются в таблицу. Инструкция SELECT не может содержать обобщенное табличное выражение (CTE).
execute_statement
Любая допустимая инструкция EXECUTE, возвращающая данные с помощью инструкций SELECT или READTEXT. Дополнительные сведения см. в разделе EXECUTE (Transact-SQL).

Параметры RESULT SETS инструкции EXECUTE нельзя указывать в инструкции INSERT…EXEC.

Если аргумент execute_statement используется с инструкцией INSERT, каждый результирующий набор должен быть совместим со столбцами в таблице или списке column_list.

Аргумент execute_statement может применяться для выполнения хранимых процедур на том же сервере или на сервере, расположенном удаленно. На удаленном сервере выполняется процедура, результирующий набор возвращается на локальный сервер и загружается в таблицу на локальном сервере. В распределенной транзакции нельзя выполнить инструкцию execute_statement для связанного сервера с замыканием на себя, если при соединении включен режим MARS (множественный активные результирующий набор).

Если execute_statement возвращает данные с помощью инструкции READTEXT, каждая инструкция READTEXT может возвращать не более 1 МБ (1024 КБ) данных. execute_statement также может использоваться при работе с расширенными процедурами. execute_statement вставляет данные, возвращенные главным потоком расширенной процедуры; однако выходные данные из других потоков (кроме главного) не вставляются.

Возвращающий табличное значение параметр нельзя указывать в качестве объекта инструкции INSERT EXEC, но его можно указать в виде источника в строке INSERT EXEC или в хранимой процедуре. Дополнительные сведения см. в разделе Использование параметров, возвращающих табличные значения (компонент Database Engine).
<dml_table_source>
Указывает, что вставленные в целевую таблицу строки были возвращены предложением OUTPUT инструкции INSERT, UPDATE, DELETE или MERGE с возможной фильтрацией предложением WHERE. Если используется аргумент <dml_table_source>, целевая таблица внешней инструкции INSERT должна удовлетворять следующим ограничениям:
- Быть базовой таблицей, а не представлением.
- Не быть удаленной таблицей.
- Не иметь определенных для нее триггеров.
- Не участвовать в связях «первичный-внешний ключ».
- Объект не должен участвовать в репликации слиянием или обновляемых подписках для репликации транзакций.
Уровень совместимости базы данных должен быть не ниже 100. Дополнительные сведения см. в разделе Предложение OUTPUT (Transact-SQL).

<select_list>
Список с разделителями-запятыми, указывающий, какие столбцы возвращены предложением OUTPUT для вставки. Столбцы в <select_list> должны быть совместимы со столбцами, в которые вставляются значения. <select_list> не может ссылаться на агрегатные функции или TEXTPTR.

Примечание
Любые перечисленные в списке SELECT переменные ссылаются на свои исходные значения, независимо от любых изменений, произошедших с ними в <dml_statement_with_output_clause>.

<dml_statement_with_output_clause>
Допустимая инструкция INSERT, UPDATE, DELETE или MERGE, возвращающая изменяемые строки в предложении OUTPUT. Инструкция не может содержать предложение WITH и использовать удаленные таблицы или секционированные представления в качестве целевых. Если указаны UPDATE или DELETE, это не могут быть использующие курсор инструкции UPDATE или DELETE. На исходные строки нельзя ссылаться как на вложенные инструкции DML.
WHERE <search_condition>
Любое предложение WHERE, содержащее допустимый критерий поиска <search_condition>, фильтрующее строки, которые возвращены аргументом <dml_statement_with_output_clause>. Дополнительные сведения см. в разделах Условие поиска (Transact-SQL). При использовании в этом контексте критерий <search_condition> не должен содержать вложенных запросов, определяемых пользователем скалярных функций, выполняющих доступ к данным, агрегатных функций, TEXTPTR или полнотекстовых предикатов поиска.
DEFAULT VALUES
Заполняет новую строку значениями по умолчанию, определенными для каждого столбца.
BULK
Используется внешними средствами для передачи потока двоичных данных. Этот параметр не предназначен для использования с такими средствами, как среда SQL Server Management Studio, SQLCMD, OSQL или прикладными программными интерфейсами для доступа к данным, такими как собственный клиент SQL Server.
FIRE_TRIGGERS
Указывает, что при передаче потока двоичных данных будут выполняться триггеры INSERT, определенные для целевой таблицы. Дополнительные сведения см. в разделе BULK INSERT (Transact-SQL).
CHECK_CONSTRAINTS
Указывает, что при передаче потока двоичных данных будет выполняться проверка всех ограничений целевой таблицы или представления. Дополнительные сведения см. в разделе BULK INSERT (Transact-SQL).
KEEPNULLS
Указывает, что пустые столбцы во время передачи потока двоичных данных должны сохранить значение NULL. Дополнительные сведения см. в разделе Сохранение значений NULL или использование значений по умолчанию при массовом импорте данных (SQL Server).
KILOBYTES_PER_BATCH = kilobytes_per_batch
Определяет приблизительное число килобайт данных в пакете как kilobytes_per_batch. Дополнительные сведения см. в разделе BULK INSERT (Transact-SQL).
ROWS_PER_BATCH =rows_per_batch
Указывает приблизительное число строк в потоке двоичных данных. Дополнительные сведения см. в разделе BULK INSERT (Transact-SQL).

Примечание. Если список столбцов отсутствует, то возникает синтаксическая ошибка.

Тип данных	Стандартная операция
char	Заполнение значения пробелами до заданной ширины столбца.
varchar	Удаление конечных пробелов до последнего непробельного символа или до одного пробела, если строка состоит только из пробелов.
varbinary	Удаление конечных нулей.

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

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

Если при выполнении инструкции INSERT возникает арифметическая ошибка (переполнение, деление на ноль или ошибка домена), компонент Ядро СУБД обрабатывает эти ошибки так же, как если бы параметру SET ARITHABORT было присвоено значение ON. Выполнение пакета прекращается и выводится сообщение об ошибке. Во время оценки выражения, когда параметры SET ARITHABORT и SET ANSI_WARNINGS установлены в значение OFF, если в инструкции INSERT, DELETE или UPDATE происходит арифметическая ошибка переполнения, деления на ноль или ошибка области определения, SQL Server вставляет или обновляет значение NULL. Если целевой столбец не пустой, вставка или обновление не осуществляются, и пользователь получает ошибку.

Совместимость

Если триггер INSTEAD OF определен в операциях INSERT для таблицы или представления, то триггер выполняется вместо инструкции INSERT. Дополнительные сведения о триггерах INSTEAD OF см. в разделе CREATE TRIGGER (Transact-SQL).

Ограничения

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

При использовании выражения TOP в инструкции INSERT строки, на которые имеются ссылки, не упорядочиваются, а предложение ORDER BY не может быть прямо указано в этих инструкциях. Если для вставки строк в значимом хронологическом порядке необходимо использовать предложение TOP, вместе с ним в инструкции подзапроса выборки следует использовать предложение ORDER BY. См. подраздел «Примеры» далее в этом разделе.

Режим ведения журнала

Инструкция INSERT всегда полностью регистрируется в журнале, кроме случаев использования функции OPENROWSET с ключевым словом BULK или выполнения инструкции INSERT INTO <target_table> SELECT <columns> FROM <source_table>. Для этих операций возможно минимальное протоколирование. Дополнительные сведения см. в подразделе «Рекомендации по массовой загрузке данных» этого раздела.

Безопасность

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

При использовании функции OPENROWSET(BULK…) важно понимать, каким образом SQL Server обрабатывает олицетворение. Дополнительные сведения см. в подразделе «Вопросы безопасности» в разделе Массовый импорт данных при помощи инструкции BULK INSERT или OPENROWSET(BULK...) (SQL Server).

Разрешения

Требуется разрешение INSERT на целевую таблицу.

Разрешения INSERT предоставлены по умолчанию членам предопределенной роли сервера sysadmin, членам предопределенных ролей баз данных db_owner и db_datawriter, а также владельцу таблицы. Члены ролей sysadmin, db_owner и db_securityadmin, а также владелец таблицы могут передавать разрешения другим пользователям.

Чтобы выполнить инструкцию INSERT с параметром BULK функции OPENROWSET, необходимо быть членом предопределенной роли сервера sysadmin или bulkadmin.

Примеры

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

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

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

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

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

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

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

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

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

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

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

USE AdventureWorks2012;
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 AdventureWorks2012;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 
(
    column_1 AS 'Computed column ' + column_2, 
    column_2 varchar(30) 
        CONSTRAINT default_name DEFAULT ('my column default'),
    column_3 rowversion,
    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 AdventureWorks2012;
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. В отличие от столбцов идентификаторов, компонент Ядро СУБД не создает автоматически значения для столбцов с типом данных uniqueidentifier, как показано во второй инструкции INSERT.

USE AdventureWorks2012;
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, которые представлены как свойства определяемого пользователем типа. Необходимо привести разделяемые запятой значения X и Y к типу Point с помощью функции CAST или CONVERT. Первые две инструкции используют функцию 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 и Person) и сохранения результирующего набора в таблице EmployeeSales . Вторая инструкция INSERT с помощью предложения EXECUTE вызывает хранимую процедуру, содержащую инструкцию SELECT, а третья инструкция INSERT с помощью предложения EXECUTE ссылается на инструкцию SELECT как на символьную строку.

USE AdventureWorks2012;
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,
  BusinessEntityID   varchar(11) NOT NULL,
  LastName     varchar(40) NOT NULL,
  SalesDollars money NOT NULL
);
GO
CREATE PROCEDURE dbo.uspGetEmployeeSales 
AS 
    SET NOCOUNT ON;
    SELECT 'PROCEDURE', sp.BusinessEntityID, c.LastName, 
        sp.SalesYTD 
    FROM Sales.SalesPerson AS sp  
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.BusinessEntityID LIKE '2%'
    ORDER BY sp.BusinessEntityID, c.LastName;
GO
--INSERT...SELECT example
INSERT INTO dbo.EmployeeSales
    SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD 
    FROM Sales.SalesPerson AS sp
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.BusinessEntityID LIKE '2%'
    ORDER BY sp.BusinessEntityID, c.LastName;
GO
--INSERT...EXECUTE procedure example
INSERT INTO dbo.EmployeeSales 
EXECUTE dbo.uspGetEmployeeSales;
GO
--INSERT...EXECUTE('string') example
INSERT INTO dbo.EmployeeSales 
EXECUTE 
('
SELECT ''EXEC STRING'', sp.BusinessEntityID, c.LastName, 
    sp.SalesYTD 
    FROM Sales.SalesPerson AS sp 
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.BusinessEntityID LIKE ''2%''
    ORDER BY sp.BusinessEntityID, c.LastName
');
GO
--Show results.
SELECT DataSource,BusinessEntityID,LastName,SalesDollars
FROM dbo.EmployeeSales;
GO

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

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

USE AdventureWorks2012;
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,
    PhoneNumber 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.BusinessEntityID, c.LastName, c.FirstName, pp.PhoneNumber,
       a.AddressLine1, a.City, sp.StateProvinceCode, 
       a.PostalCode, e.CurrentFlag
    FROM HumanResources.Employee e
        INNER JOIN Person.BusinessEntityAddress AS bea
        ON e.BusinessEntityID = bea.BusinessEntityID
        INNER JOIN Person.Address AS a
        ON bea.AddressID = a.AddressID
        INNER JOIN Person.PersonPhone AS pp
        ON e.BusinessEntityID = pp.BusinessEntityID
        INNER JOIN Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
        INNER JOIN Person.Person as c
        ON e.BusinessEntityID = c.BusinessEntityID
    )
INSERT INTO HumanResources.NewEmployee 
    SELECT EmpID, LastName, FirstName, Phone, 
           Address, City, StateProvince, PostalCode, CurrentFlag
    FROM EmployeeTemp;
GO

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

В следующем примере создается таблица EmployeeSales и вставляется имя и данные о продажах за текущий год для 5 наиболее успешных сотрудников, случайно выбираемых в таблице HumanResources.Employee. Инструкция INSERT выбирает любые пять строк из строк, возвращенных инструкцией SELECT. Предложение OUTPUT отображает строки, вставляемые в таблицу EmployeeSales. Обратите внимание, что предложение ORDER BY в инструкции SELECT не используется для определения 5 наиболее успешных сотрудников.

USE AdventureWorks2012 ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID   nvarchar(11) NOT NULL,
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  YearlySales  money NOT NULL
 );
GO
INSERT TOP(5)INTO dbo.EmployeeSales
    OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.YearlySales
    SELECT sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD 
    FROM Sales.SalesPerson AS sp
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.SalesYTD > 250000.00
    ORDER BY sp.SalesYTD DESC;

Если для вставки строк в значимом хронологическом порядке решено использовать предложение TOP, вместе с ним в инструкции подзапроса выборки следует использовать предложение ORDER BY, как показано в следующем примере. Предложение OUTPUT отображает строки, вставляемые в таблицу EmployeeSales. Обратите внимание, что вставка данных 5 наиболее успешных сотрудников выполняется на основе результатов предложения ORDER BY, а не случайных строк.

INSERT INTO dbo.EmployeeSales
    OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.YearlySales
    SELECT TOP (5) sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD 
    FROM Sales.SalesPerson AS sp
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.SalesYTD > 250000.00
    ORDER BY sp.SalesYTD DESC;

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

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

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

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

USE AdventureWorks2012;
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 AdventureWorks2012;
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 указывается в качестве одного из четырех компонентов имени объекта в форме server.catalog.schema.object.

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'AdventureWorks2012';
GO

USE AdventureWorks2012;
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.AdventureWorks2012.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 AdventureWorks2012.HumanResources.Department')
VALUES ('Environmental Impact', 'Engineering');
GO

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

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

-- 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')
    .AdventureWorks2012.HumanResources.Department (Name, GroupName)
    VALUES (N'Standards and Methods', 'Quality Assurance');
GO

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

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

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

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

USE AdventureWorks2012;
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 AdventureWorks2012
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 AdventureWorks2012
SET RECOVERY FULL;
GO

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

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

-- 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 и сохраняется до завершения инструкции UPDATE.

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

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

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

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

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

USE AdventureWorks2012;
GO
DECLARE @MyTableVar table( NewScrapReasonID 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 NewScrapReasonID, 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). Поскольку значения создаются компонентом Ядро СУБД при вставке, ни один из этих столбцов не может быть определен в @MyTableVar.

USE AdventureWorks2012 ;
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 Sales.SalesPerson AS sp
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.BusinessEntityID 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. Инструкция также удаляет строки с продуктами, запас которых сократился до 0. В примере собираются удаленные строки и вставляются в другую таблицу, ZeroInventory, в которой ведется учет закончившихся продуктов.

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

INSERT INTO Production.ZeroInventory (DeletedProductID, RemovedOnDate)
SELECT ProductID, GETDATE()
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 = '20070401'
           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';
IF @@ROWCOUNT = 0
PRINT 'Warning: No rows were inserted';
GO
SELECT DeletedProductID, RemovedOnDate FROM Production.ZeroInventory;