On identity insert: How to turn IDENTITY_INSERT on and off using SQL Server 2008?

Содержание

Вставка идентификатора SQL Server для синхронизации таблиц

Проблема

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

Еще одна вещь, часто встречающаяся в SQL Server, — использование значений идентификаторов.
или автоматическое увеличение значения ключа для новых записей по мере их вставки.
Использование значений идентификаторов — это простой способ убедиться, что у вас есть уникальный первичный ключ.
для новых записей, но нет простого способа контролировать, какое значение идентификатора будет
задается для определенной строки в таблице.

При объединении этих двух элементов вместе с столбцом идентификаторов в качестве основного
ключ и необходимость передавать подобные данные в другие базы данных, вот где проблемы
начинать. Если ваши таблицы в этих разных базах данных настроены точно так же
способ, когда обе таблицы имеют значение идентификатора, невозможно контролировать, какой идентификатор
значение, которое одна таблица получит по сравнению с другой таблицей, и поэтому у вас могут возникнуть проблемы, когда
данные не синхронизированы.

Решение

Чтобы справиться с этой ситуацией, когда вам нужно убедиться, что данные в ваших двух базах данных
остается синхронизированным вместе со значением идентификатора для ключа, SQL Server имеет
команда SET IDENTITY_INSERT , позволяющая включать и
отключить автоматическую генерацию значения идентификатора по мере необходимости. Команда получает
включается перед вставкой данных и выключается после того, как данные
был вставлен.

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

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

 INSERT INTO dbo.CLIENT (ClientName) VALUES ("Edgewood Solutions")
ВСТАВИТЬ В dbo.CLIENT (имя клиента) ЗНАЧЕНИЯ ("Microsoft")

После вставки данных у нас есть две новые записи в нашей таблице Client.
Значение ClientID было создано автоматически.

Идентификатор клиента	ИмяКлиента
782	Решения Эджвуд
783	Майкрософт

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

 УСТАНОВИТЬ IDENTITY_INSERT dbo.Client ON
ВСТАВИТЬ В dbo.CLIENT (ClientID, ClientName) ЗНАЧЕНИЯ (782, «Edgewood Solutions»)
ВСТАВЬТЕ В dbo.CLIENT (ClientID, ClientName) ЗНАЧЕНИЯ (783, 'Microsoft')
УСТАНОВИТЬ IDENTITY_INSERT dbo.Client ВЫКЛ.

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

Следующие шаги

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

Об авторе

Грег Робиду является президентом и основателем Edgewood Solutions, компании, предоставляющей технологические услуги и предоставляющей услуги и решения для Microsoft SQL Server. Он также является одним из соучредителей MSSQLTips. com. Грег работает с SQL Server с 19 лет.99, является автором многочисленных статей, связанных с базами данных, и представил несколько презентаций, связанных с SQL Server. До SQL Server он работал над многими платформами данных, такими как DB2, Oracle, Sybase и Informix.

Посмотреть все мои советы

Последнее обновление статьи: 09.07.2020

MS SQL Server: Identity Field, Identity Insert an…

Hello

У меня очень смешанные результаты при использовании формы Alteryx «Выходные данные» с полем int первичного ключа в таблице SQL Server, которая установлена как поле идентификации. В частности, как Alteryx обрабатывает вставки для этого поля при использовании функции вставки удостоверений SQL Server, т. Е. Мне нужно (для этой конкретной ситуации) иметь возможность вставлять определенные значения в поле удостоверения. В большинстве инструментов после включения Identity Insert данные могут быть вставлены в поле так же, как и в любое другое поле, однако Alteryx, похоже, не поддерживает это согласованным образом.

Итак, я провел несколько тестов, пытаясь понять, как лучше всего это сделать, и выяснить, поддерживается ли это официально Alteryx или нет.

О полях идентификации: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property

Параметры теста

База данных с именем «Test4».

Простая таблица с именем Table_1 с двумя столбцами: id, int, identity(1, 1) и data varchar(50).

В фигуре выходных данных оператор SQL перед созданием: SET IDENTITY_INSERT Table_1 ON.

В фигуре выходных данных оператор Post Create SQL: SET IDENTITY_INSERT Table_1 OFF.

Ссылка на вставку удостоверения: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql Сервер…»

Это то, как Alteryx направляет вас, т.е. в форме выходных данных щелкните стрелку вниз и в меню выберите «Microsoft SQL Server…».

При этом создается подключение вида: aka:LocalHost Test4|||Table_1

Определение этого подключения в Параметры >> Дополнительные настройки >> Управление подключениями к базе данных:

odbc:DRIVER={SQL Server Native Client 11. 0} ;DATABASE=Test4;SERVER=.;Trusted_Connection=yes

Результат теста: Ошибка : Выходные данные (3): DataWrap2ODBC::SendBatch: [Microsoft][SQL Server Native Client 11.0][SQL Server] Explicit значение должно быть указано для столбца идентификаторов в таблице «Таблица_1» либо в том случае, если для параметра IDENTITY_INSERT установлено значение ON, либо когда пользователь репликации вставляет данные в столбец идентификаторов NOT FOR REPLICATION .[Microsoft][SQL Server Native Client 11.0][SQL Server]Для столбца идентификаторов в таблице Table_1 должно быть указано явное значение либо в том случае, если для параметра IDENTITY_INSERT установлено значение ON, либо когда пользователь репликации вставляет данные в столбец идентификаторов NOT FOR REPLICATION. .[Microsoft][SQL Server Native Client 11.0][SQL Server]Для столбца идентификаторов в таблице Table_1 должно быть указано явное значение либо в том случае, если для параметра IDENTITY_INSERT установлено значение ON, либо когда пользователь репликации вставляет данные в столбец идентификаторов NOT FOR REPLICATION. Вставить в «Таблица_1» («данные») значения (?)

NB. Поле идентификатора определенно отображается в фигуре выходных данных.

Я также пробовал вышеописанное, используя DSN ODBC с последним драйвером ODBC для SQL Server от Microsoft, настроив DSN в источниках данных Windows (ODBC), которые имели следующее определение:

Драйвер Microsoft ODBC для SQL Версия сервера 13.00.1601

Имя источника данных: Localhost Test 4 SQLODBC2013
Описание источника данных:
Сервер: localhost
Использование встроенной безопасности: Да
База данных: Test4
Язык: (по умолчанию)
Шифрование данных: Нет
Сертификат сервера доверия: Нет
Несколько активных наборов результатов (MARS): Нет
Зеркальный сервер:
Перевод символьных данных: Да
Длинный журнал Выполняемые запросы: Нет
Статистика драйвера журнала: Нет
Использовать региональные настройки: Нет
Использовать идентификаторы ANSI в кавычках: Да
Использовать пустые значения ANSI, отступы и предупреждения: Да

Тест 2. Создание подключения OLEDB
В фигуре «Выходные данные» нажмите стрелку вниз и в меню выберите «Другие базы данных…» >> «OLEDB…».
При этом создается соединение со следующим определением:
odb:Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User;Initial Catalog=Test4;Data Source=.;Использовать процедуру для подготовки=1. ;Автоперевод=Истина;Размер пакета=4096;Идентификатор рабочей станции=CND5115649;Исходное имя файла=»»;Использовать шифрование для данных=False;Тег с сопоставлением столбцов, когда это возможно=False;Соединение MARS=False;DataTypeCompatibility=0;Сертификат доверенного сервера=False;Намерение приложения=READWRITE|||
Результат проверки: Успешно, строки вставлены правильно с правильными значениями идентификаторов (т. е. ВСТАВКА ИДЕНТИФИКАЦИОННОЙ ИНФОРМАЦИИ прошла успешно).

Тест 3. Массовое использование SQL Server
В фигуре «Выходные данные» нажмите стрелку вниз и в меню выберите «Другие базы данных. ..» >> «Макет SQL Server».
При этом создается соединение со следующим определением:
ssvb:DSN=Localhost Test 4 Native Client|||
т. е. это должна быть ссылка на источник данных, настроенный в источниках данных Windows (ODBC).
Определение вышеприведенного DSN:
- Версия собственного клиента Microsoft SQL Server 11.00.6518
- Имя источника данных: Localhost Test 4 Native Client
  Описание источника данных:
  Сервер: localhost
  Использование встроенной безопасности: Да
  База данных: Test4
  Язык: (по умолчанию)
  Шифрование данных: Нет
  Сертификат сервера доверия: Нет
  Несколько активных наборов результатов (MARS): Нет
  Зеркальный сервер:
  Перевод символьных данных: Да
  Журнал длительных запросов: Нет
  Журнал Статистика драйвера: Нет
  Использовать региональные настройки: Нет
  Использовать идентификаторы ANSI в кавычках: Да
  Использовать ANSI Null, Paddings и предупреждения: Да значения идентичности были сгенерированы).