2.16. Добавление записей

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

Теперь давайте познакомимся с командами добавления данных в таблицу. Когда мы вначале главы заполняли таблицу данными, то вы должны были просто выполнить сценарий. Сейчас мы познакомимся с командой INSERT, которая добавляет в таблицу запись и вы сможете понять, как работал сценарий заполнения таблицы Chapter2/fill_data.sql.

Листинг 2.4. Общий вид команды INSERT

INSERT [ INTO] 
    { table_name WITH ( < table_hint_limited > [ ...n ] ) 
        | view_name 
        | rowset_function_limited 
    } 

    {    [ ( column_list ) ] 
        { VALUES 
            ( { DEFAULT | NULL | expression } [ ,...n] ) 
            | derived_table 
            | execute_statement 
        } 
    }

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

INSERT INTO tbPeoples 
DEFAULT VALUES

Посмотрите, что добавил сервер в таблицу tbPeoples. Как? Нужно посмотреть последнюю строку, а этот запрос мы рассматривали в разделе 2.14. Напоминаю, как он выглядел:

SELECT * 
FROM tbPeoples 
WHERE idPeoples=
    (SELECT MAX(idPeoples) FROM tbPeoples)
В результате вы должны увидеть следующую теблицу:
idPeoples  vcFamil  vcName  vcSurname  idPosition  dDateBirthDay                                          
----------------------------------------------------------------
20         NULL     NULL    NULL       NULL        NULL

(1 row(s) affected)

В таблице tbPeoples первое поле 'idPeoples' является автоматически увеличиваемым, и новой строке это поле стало равным 20. Остальные поля не имеют значений по умолчанию и тут можно видеть только нулевые значения NULL.

Вернемся к запросу добавления записи и рассмотрим его подробнее. В данном запросе выполняется оператор INSERT INTO (вставить в), после которого указывается имя таблицы, в которую нужно вставить запись. Далее может идти описание имен полей, которые нужно заполнять или список значений. В нашем случае нет ни того, ни другого, зато есть DEFAULT VALUES (значения по умолчанию), который указывает на необходимость заполнить все поля значениями по умолчанию.

В таблице tbPeoples только у одного поля указывается значение по умолчанию – это автоматически увеличиваемое поле первичного ключа. Хотя мы не указывали DEFAULT значение, автоматически увеличиваемое поле обязательно получает значение, тем более что это первичный ключ, и он должен быть заполнен. Все остальные поля будут нулевыми.

Следующий запрос пытается добавить запись с явно указанными данными:

INSERT INTO tbPeoples 
VALUES(122, 'ИВАНОВ', 'ИВАН', 'СЕРГЕЕВИЧ', 1, '01.01.1971')

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

В данном запросе указаны все поля и указаны в соответствии со всеми типами, но почему я сказал, что этот запрос «пытается добавить строку»? Ничего в голову не приходит? Первое поле имеет тип автоматического увеличиваемого числа. Это поле изменять нельзя, поэтому сервер вернет ошибку:

An explicit value for the identity column in table 'tbPeoples' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Явное значение для авто увеличиваемой колонки в таблице 'tbPeoples' может быть указано только когда используется список колонок и свойство IDENTITY_INSERT установлено в ON.

Неужели нельзя добавить строку? Конечно же, есть способ решения этой проблемы, просто необходимо явным образом задать список колонок, которые должны изменяться и установить им нужные значения.

Список колонок указывается в скобках после имени таблицы:

INSERT INTO tbPeoples 
 (idPeoples, vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) 
VALUES (122, 'ИВАНОВ', 'ИВАН', 'СЕРГЕЕВИЧ', 1, '01.01.1971')

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

Но этого не достаточно. Необходимо установить нужное свойство. Мы этого пока свойства не изменяли, но это делается с помощью оператора SET. Затем идет имя свойства (IDENTITY_INSERT), имя таблицы, свойство которой нужно изменить и напоследок – значение (чаще всего ON или OFF). В виде Transact-SQL команды это выглядит следующим образом:

SET IDENTITY_INSERT tbPeoples ON

Необходимо заметить, что изменение подобных свойств относится только к Transact-SQL и MS SQL Server.

Теперь посмотрим на полную SQL команду добавления записи с явным указанием значения для автоматически увеличиваемого значения:

SET IDENTITY_INSERT tbPeoples ON
INSERT INTO tbPeoples 
(idPeoples, vcFamil, vcName, vcSurname, 
    idPosition, dDateBirthDay) 
VALUES(122, 'ИВАНОВ', 'ИВАН', 'СЕРГЕЕВИЧ', 
    1, '01.01.1971')

После выполнения команды желательно вернуть значение параметра IDENTITY_INSERT в OFF:

SET IDENTITY_INSERT tbPeoples OFF

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

Как добавить строку, и при этом не указывать значение ключевого поля, чтобы сервер смог его установить самостоятельно? Очень просто. Дело в том, что перечислять все поля необязательно. Можно указать только те имена полей, значения которых вы хотите явно изменить. Например, в следующем запросе мы не указываем значение для ключевого поля "idPeoples":

INSERT INTO tbPeoples 
 (vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) 
VALUES('ИВАНОВ', 'ИВАН', 'АЛЕКСЕЕВИЧ', 1, '01.01.1971')

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

Давайте добавим строку, в которой укажем только фамилию работника. Для этого, в списке полей нужно указать только имя поля "vcName", а в списке значений должно быть только одно строковое значение, ведь фамилия имеет строковый тип:

INSERT INTO tbPeoples (vcFamil) 
VALUES('ПЕТРОВ')

Если в вашей программе удобнее сделать перечисление всех полей, но при этом заполняются далеко не все значения, вместо не заполняемых значений можно указать значение NULL или DEFAULT. В следующем примере, вместо фамилии и даты рождения указано ключевое слово DEFAULT и этим полям сервер установит значение по умолчанию. Для поля фамилии указывается значение NULL:

INSERT INTO tbPeoples 
 (vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) 
VALUES('СЕРГЕЕВ', DEFAULT, NULL, 1, DEFAULT)

Ключевые слова DEFAULT и NULL можно указывать для любых типов полей, кроме автоматически увеличиваемого, потому что это поле не может содержать NULL и не имеет значения по умолчанию. Автоматическое увеличение к значению по умолчанию не относиться.

В перечислении можно указывать поля в любом порядке, и только те, которые необходимы. При этом в списке VALUES значения должны идти в том же порядке, в котором вы их перечисляли. Например, в следующем примере поля фамилии, имени и отчества заполняются в обратном порядке. При этом между отчеством и именем идет поле "idPosition":

INSERT INTO tbPeoples 
 (vcSurname, idPosition, vcName, vcFamil) 
VALUES('ПЕТРОВИЧ', 12, 'СЕРГЕЙ', 'СМИРНОВ')

В разделе 2.6 мы рассматривали, как можно использовать SELECT INTO для создания новой таблицы. Теперь давайте посмотрим, как можно импортировать данные в уже существующую таблицу, ведь SELECT INTO не может выбирать данные в таблицу, если она уже существует.

Для этого создадим новую таблицу tbPeoples2, которая будет состоять из таких же полей, как и у tbPeoples. Единственная разница, ключевое поле не будет автоматически увеличиваемым, чтобы сервер не ругался, на вставку значений в первичный ключ. Посмотрите на SQL код создания новой таблицы tbPeoples2, показан в листинге 2.5.

Листинг 2.5. Создание новой таблицы, копии tbPeoples

CREATE TABLE tbPeoples2
(
  -- Описание полей
  idPeoples2 int,
  vcFamil varchar(50),
  vcName varchar(50),
  vcSurname varchar(50),
  idPosition int, 
  dDateBirthDay datetime, 

  -- Описание ключей
  CONSTRAINT PK_idPeoples2 PRIMARY KEY (idPeoples2),
  CONSTRAINT FK_idPosition2 FOREIGN KEY (idPosition)
     REFERENCES tbPosition (idPosition), 

  -- Описание ограничений
  CONSTRAINT check_dDateBirthDay2 CHECK (dDateBirthDay<getdate())
)

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

Теперь посмотрим, как заполнить новую таблицу записями работников из таблицы tbPooples, и при этом взять только те записи, у которых поле "idPosition" содержит значение более или равное 10. Все достаточно просто:

INSERT INTO tbPeoples2
SELECT *
FROM tbPeoples
WHERE idPosition>=10

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

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

CREATE TABLE tbPeoples3
(
  -- Описание полей
  vcFamil varchar(50),
  vcName varchar(50),
  dDateBirthDay datetime, 
)

Теперь посмотрим, как можно заполнить эту таблицу значениями:

INSERT INTO tbPeoples3
SELECT vcFamil, vcName, dDateBirthDay 
FROM tbPeoples
WHERE idPosition>=10

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

С помощью оператора INSERT INTO вы можете копировать данные между таблицами или даже переносить их с сервера на сервер с помощью распределенных запросов, о которых мы поговорим в разделе 5.1.

Запрос SELECT в INSERT INTO может быть любой сложности, и тут ограничений на используемые операторы нет. Другое дело, что не всегда сортировка может иметь смысл. Например, если вставлять отсортированные данные в таблицу tbPeoples, то таким образом можно повлиять только на идентификатор добавляемых строк, потому что тут автоматически увеличиваемое поле, которое генерируется последовательно. Но если бы первичный ключ был GUID полем, то сортировка потеряла бы смысл, потому что GUID поле генерируется случайным образом.