Статья
04/01/2012

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

Добавляет одну или несколько новых строк в таблицу или представление в SQL Server 2008 R2. Примеры см. в разделе Примеры использования инструкции INSERT (Transact-SQL).

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

Синтаксис

-- Standard INSERT syntax
[ 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 
        }
    }
}
[; ]

-- Syntax for external tool only
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 ]
           ) ]
}
[; ]

<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 ]

Аргументы

WITH <common_table_expression>
Определяет временный именованный результирующий набор, также называемый обобщенным табличным выражением, определенным в области инструкции INSERT. Результирующий набор получается из инструкции SELECT.

Обобщенные табличные выражения также используются инструкциями SELECT, DELETE, UPDATE, MERGE и CREATE VIEW. Дополнительные сведения см. в разделе WITH обобщенное_табличное_выражение (Transact-SQL).
TOP (expression) [ PERCENT ]
Задает количество или процент случайных строк для вставки. Выражение expression может быть либо количеством, либо процентом строк. Строки, на которые ссылается выражение TOP, используемое с INSERT, UPDATE и DELETE, не упорядочены.

Инструкции INSERT, UPDATE и DELETE требуют заключения аргумента expression в круглые скобки в выражении TOP. Дополнительные сведения см. в разделе 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, то компонент Database Engine должен обеспечить значение, основанное на определении столбца; в противном случае строку нельзя будет загрузить. Компонент Database Engine автоматически задает значение для столбца, если столбец имеет следующие характеристики.
- Имеется свойство 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
Указывает компоненту Database Engine необходимость принудительно загружать значения по умолчанию, определенные для столбца. Если для столбца не задано значение по умолчанию и он может содержать значение 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_statement используется с инструкцией INSERT, каждый результирующий набор должен быть совместим со столбцами в таблице или списке column_list.

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

Если аргумент execute_statement возвращает данные с инструкцией READTEXT, необходимо учитывать, что каждая инструкция READTEXT может возвращать не более 1 МБ (1024 КБ) данных. Аргумент 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 или использование значений по умолчанию при массовом импорте данных.
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 возникает арифметическая ошибка (переполнение, деление на ноль или ошибка домена), компонент Database Engine обрабатывает эти ошибки так же, как если бы параметру SET ARITHABORT было присвоено значение ON. Выполнение пакета прекращается и выводится сообщение об ошибке. Во время оценки выражения, когда параметры SET ARITHABORT и SET ANSI_WARNINGS установлены в значение OFF, если в инструкции INSERT, DELETE или UPDATE происходит арифметическая ошибка переполнения, деления на ноль или ошибка области определения, SQL Server вставляет или обновляет значение NULL. Если целевой столбец не пустой, вставка или обновление не осуществляются, и пользователь получает ошибку. Дополнительные сведения см. в разделах Режим работы при значении ON параметров ARITHABORT и ARITHIGNORE.

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

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

Ограничения

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

Инструкции INSERT не учитывает настройки параметра SET ROWCOUNT в местных и расположенных удаленно секционированных представлениях. Кроме того, этот параметр не поддерживается для инструкций INSERT, применяемых к удаленным таблицам.

Важно!
Использование инструкции SET ROWCOUNT не будет оказывать влияния на инструкции DELETE, INSERT и UPDATE в следующей версии SQL Server. В новых разработках следует избегать совместного использования инструкции SET ROWCOUNT с инструкциями DELETE, INSERT и UPDATE. Также необходимо запланировать внесение изменений в используемые приложения. Вместо нее рекомендуется использовать предложение TOP.

Использование инструкции SET ROWCOUNT не будет оказывать влияния на инструкции DELETE, INSERT и UPDATE в следующей версии SQL Server. В новых разработках следует избегать совместного использования инструкции SET ROWCOUNT с инструкциями DELETE, INSERT и UPDATE. Также необходимо запланировать внесение изменений в используемые приложения. Вместо нее рекомендуется использовать предложение TOP.

Режим блокировки

Инструкция INSERT всегда получает монопольную блокировку (X) на таблицу, которую она изменяет, и держит блокировку до тех пор, пока транзакция не завершится. Если ресурс удерживается монопольной (X) блокировкой, то другие транзакции не могут изменять данные. Операции считывания будут допускаться только при наличии подсказки NOLOCK или уровня изоляции незафиксированной операции чтения. Дополнительные сведения см. в разделе Блокировка в компоненте Database Engine.

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

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

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

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

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

Разрешения

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

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

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