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

  • Статья

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

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

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

Аргументы

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

    Обобщенные табличные выражения также используются инструкциями SELECT, DELETE, UPDATE и 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. Избегайте применять их в новых разработках и запланируйте внесение изменений в приложения, использующие их в настоящее время.

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

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

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

    • Имеется свойство IDENTITY. Используется следующее значение приращения для идентификатора.

    • Имеется стандартное значение. Используется стандартное значение для столбца.

    • Имеет тип данных timestamp. В этом случае используется текущее значение 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 для явного указания столбца, в котором хранится каждое входное значение.

    В SQL Server 2008 добавлен конструктор строк Transact-SQL (также называемый конструктором табличных значений), позволяющий указать несколько строк в одной инструкции INSERT. Этот конструктор строк состоит из одного предложения VALUES со списками из нескольких значений, заключенными в круглые скобки и разделенными запятыми. Дополнительные сведения см. в разделе Конструктор табличных значений (Transact-SQL).

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

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

    При ссылке на символьные типы данных nchar, nvarchar и ntext в Юникоде «выражение» должно начинаться с заглавной буквы «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.

  • <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
    Заполняет новую строку значениями по умолчанию, определенными для каждого столбца.

Рекомендации по массовой загрузке данных

Использование инструкции INSERT INTO…SELECT для массовой загрузки данных с минимальным протоколированием

Инструкция INSERT INTO <целевая_таблица> SELECT <столбцы> FROM <исходная_таблица> может эффективно перенести большое количество строк из одной таблицы (например, промежуточной) в другую таблицу с минимальным протоколированием. Минимальное протоколирование может повысить производительность инструкции и снижает вероятность того, что в ходе операции будет исчерпано место в журнале транзакций.

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

  • Модель восстановления базы данных настроена на простое или неполное протоколирование.

  • Целевой таблицей является пустая или непустая куча.

  • Целевая таблица не используется в репликации.

  • Для целевой таблицы указана подсказка TABLOCK.

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

В отличие от инструкции BULK INSERT, которая удерживает менее строгую блокировку массового обновления, инструкция INSERT INTO…SELECT с подсказкой TABLOCK удерживает монопольную блокировку (X) таблицы. Это означает, что не поддерживается возможность вставки строк с помощью параллельных операций вставки. Дополнительные сведения о блокировке см. в разделе Режимы блокировки.

Использование предложений OPENROWSET и BULK для массовой загрузки данных

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

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

  • Подсказка IGNORE_CONSTRAINTS может временно отключить проверку ограничений FOREIGN KEY и CHECK.

  • Подсказка IGNORE_TRIGGERS может временно отключить выполнение триггеров.

  • Подсказка KEEPDEFAULTS позволяет вставлять значение по умолчанию для столбца таблицы (если оно имеется) вместо значения NULL, когда в записи данных отсутствует значение для столбца.

  • Подсказка KEEPIDENTITY позволяет использовать значения идентификаторов в импортированном файле данных для столбца идентификаторов в целевой таблице.

Эти оптимизации похожи на оптимизации, доступные для команды BULK INSERT. Дополнительные сведения см. в разделе Табличные подсказки (Transact-SQL).

Типы данных

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

  • Если значение загружается в столбцы с типом данных char, varchar или varbinary, то дополнение или усечение конечных пробелов (пробелы для char и varchar, нули для varbinary) определяет параметр SET ANSI_PADDING, определенный для столбца при создании таблицы. Дополнительные сведения см. в разделе SET ANSI_PADDING (Transact-SQL).

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

    Тип данных

    Стандартная операция

    char

    Заполнение значения пробелами до заданной ширины столбца.

    varchar

    Удаление конечных пробелов до последнего непробельного символа или до единственного пробела, если строка состоит только из пробелов.

    varbinary

    Удаление конечных нулей.

  • Если пустая строка ('') загружена в столбец с типом данных varchar или text, то операцией по умолчанию будет загрузка строки нулевой длины.

  • Вставка значения NULL в столбец text или image не приводит ни к созданию допустимого текстового указателя, ни к предварительному распределению 8-килобайтной текстовой страницы. Дополнительные сведения о вставке данных text и image см. в разделе Применение функций для работы с типами данных text, ntext и image.

  • Столбцы, созданные с типом данных uniqueidentifier, содержат двоичные 16-байтные величины специального формата. В отличие от столбцов идентификаторов компонента Database Engine не создает автоматически значения для столбцов с типом данных uniqueidentifier. Во время операции вставки переменные с типом данных uniqueidentifier и строковые константы вида xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (36 символов, включая дефисы, где x — шестнадцатеричная цифра в диапазоне от 0-9 или a-f) можно использовать для столбцов uniqueidentifier. Например, 6F9619FF-8B86-D011-B42D-00C04FC964FF является допустимым значением переменной uniqueidentifier или столбца. Используйте функцию NEWID() для получения идентификатора GUID.

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

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

  • Предоставление значения определяемого пользователем типа.

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

    INSERT INTO Cities (Location)
VALUES ( CONVERT(Point, '12.3:46.2') );

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

  • Вызов пользовательской функции, которая возвращает значение определяемого пользователем типа. В следующих примерах используется определяемая пользователем функция CreateNewPoint() для создания новых значений определяемого пользователем типа Point и вставки значения в таблицу Cities.

    INSERT INTO Cities (Location)
VALUES ( dbo.CreateNewPoint(x, y) );

Обработка ошибок

Можно реализовать обработку ошибок для инструкции INSERT, включив инструкцию в конструкцию TRY…CATCH. Дополнительные сведения см. в разделе Использование конструкции TRY...CATCH в языке Transact-SQL.

Если инструкция 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.

Правила ведения журнала

Для инструкции 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.

Примеры

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