Вставка данных

Tsql теория > Вставка данных
26.02.2013 11:05:40



Статья:

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

Вставка одной строки значений

Простейший и самый непосредственный метод вставки данных заключается в использовании инструкции INSERT. . .VALUES. Так как эта форма принимает только один набор значений, она ограничена вставкой в таблицу только одной строки. Интерфейс пользователя имеет тенденцию принимать ввод только одной строки данных, так что этот метод считается наиболее предпочтительным для использования с формами.

INSERT [INTO] владелец.таблица [(столбец, . . .) ]

VALUES [значение, . . .]

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

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

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

Если бы столбец Qualification имел значение по умолчанию, оно было бы занесено в него вместо пустого. Если бы в ограничениях столбца был установлен запрет пустых значений, то эта инструкция не была бы выполнена. (Более подробно о вставке пустых значений и значений по умолчанию мы поговорим в разделе “Потенциальные препятствия на пути модификации данных”.)

Потенциально можно форсировать в инструкции INSERT вставку значений по умолчанию, даже не зная об их существовании. Для этого в списке столбцов/значений используют ключевое слово DEFAULT; при этом SQL Server запоминает указанное значение. Такой прием часто используется на практике, поскольку это позволяет задокументировать намерения, а не полагаться на наличие значений по умолчанию.

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

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

Вставка результирующего набора данных инструкции SELECT

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

INSERT [INTO] владелец, таблица SELECT столбцы

FROM источники_даиных [WHERE условия]

например:

INSERT dbo.Contact (FirstName, ContactCode, LastName, CompanyName) SELECT FirstName, LastName, GuidelD, 'Cape Hatteras Adv.'

FROM CHA2.dbo.Guide

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

Форма INSERT. . . EXEC инструкции вставки использует результаты выполнения хранимой процедуры для их вставки в таблицу. В данном случае можно использовать все возможности языка Т-SQL. Базовая функция вставки является такой же, как и во всех ее остальных формах. Порядок столбцов в списке инструкции INSERT и в результатах хранимой процедуры должен быть одинаковым. Базовый синтаксис этой инструкции:

INSERT [INTO] владелец. таблица [{столбцы)]

ЕХЕС хранимая_процедура параметры

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

Создание инструкции INSERT. . . EXEC требует гораздо больше работы, чем INSERT. . . VALUES или INSERT. . . SELECT. В то же время хранимые процедуры могут содержать сложные логические схемы, и поэтому форма INSERT. . . EXEC является самой мощной из трех

Создание строки со значениями по умолчанию

Язык SQL имеет особую форму инструкции INSERT, которая создает новую строку, содержащую только значения столбцов по умолчанию. Единственным параметром такой инструкции является имя таблицы, при этом значения и имена столбцов не требуются и не принимаются во внимание. Синтаксис такой инструкции простой:

INSERT владелец.таблица DEFAULT VALUES

Лично я никогда не использовал эту форму инструкции INSERT на практике. Тем не менее, если вам когда-либо потребуется наполнить таблицу массой столбцов, содержащих значения, принятые по умолчанию, форма INSERT. . . DEFAULT может оказаться полезной.

Создание таблицы в процессе вставки данных

Последний метод вставки данных использует одну из вариаций инструкции SELECT. Используемый в ней параметр INTO принимает результирующий набор данных от инструкции SELECT и создает на его основе новую таблицу. Форму SELECT. . . INTO часто используют в операциях преобразования данных и в утилитах, которые должны работать с массой различных структур таблиц. Полный синтаксис этой формы включает все параметры инструкции SELECT, поэтому здесь мы приведем его усеченную версию:

SELECT столбцы

INTO новая_ та блица

FROM источники_данных

[WHERE условия]

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

Инструкцию SELECT. . . INTO можно рассматривать как операцию массового заполнения, аналогичную BULK INSERT и BULK COPY. Массовые операции позволяют серверу быстро перемещать данные в таблицы в обход процесса протоколирования транзакций (в принципе, все зависит от используемой модели восстановления данных). Параметры базы данных и модель восстановления влияют на инструкцию SELECT. . . INTO и прочие массовые операции. Если модель восстановления отличается от полной модели, то инструкция SELECT. . . INTO заноситься в журнал транзакций не будет.

Руководство по проектированию стиля данных

Существуют потенциальные проблемы, связанные с данными, которые выходят за рамки их типов, ограничений и допустимости пустых значений. Подобно тому, как средства проверки орфографии и грамматики программы Microsoft Word могут выявить очевидные ошибки (правда, не обратят внимание на плохой литературный стиль), база данных также может защитить от больших логических ошибок. Издатели в этом процессе руководствуются справочниками стилей и методическими рекомендациями. Например, скажите, как правильно йа-звать корпорацию Microsoft в книге: MS, Microsoft Corp. или Microsoft Corporation? Ответ на этот вопрос зависит от выбранного стиля.

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

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

Инструкция SELECT. . . INTO может выполнять множество полезных функций.

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

¦ Если инструкция SELECT реорганизует столбцы или содержит функцию cast (), то новая таблица будет содержать данные с модифицированной схемой.

¦ В комбинации с оператором UNION инструкция SELECT. . . INTO может комбинировать по вертикали данные из нескольких таблиц. При этом оператор INTO должен находиться в первой инструкции SELECT объединения.

¦ Инструкция SELECT. . . INTO особенно полезна для денормализации таблиц. Она может комбинировать данные из разных таблиц и помещать их в одну.

При использовании инструкции select . . . into нельзя попадаться в одну ловушку — эта инструкция не способна заменить объединения и представления. Когда создается новая таблица, она представляет собой всего лишь мгновенный снимок данных (проще говоря, вторую их копию). Базы данных, содержащие многочисленные копии старых наборов данных, являются источником постоянных проблем. Если вам нужно денормализовать данные для одноразового анализа или для передачи некоторому пользователю, то создание представления является куда лучшей альтернативой.