Главная » Microsoft SQL Server, Базы данных » Вставка данных

0

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

Таблица 16.1. Формы операций вставки

Форма вставки Описание
INSERT/VALUES Вставляет одну строку значений. Обычно используется для поддержки интерактивного ввода данных пользователем
INSERT/SELECT Вставляет в таблицу результирующий набор данных вложенного подзапроса
INSERT/EXEC Вставляет в таблицу результаты хранимой процедуры. Обычно используется в сложных задачах манипулирования данными
INSERT DEFAULT Создает новую строку с применением всех значений, заданных по умолчанию. Обычно используется для вставки общепринятых полей строк данных
SELECT INTO Создает новую таблицу из результирующего набора данных инструкции select

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

Дополштелй^ая SQL Server сопровождает инструкции insert великим множеством средств пре- инфоршция образования данных и манипулирования ими. Популярные мастера Bulk Сору — Wizard и Copy Database Wizard будут представлены в главе 35. Последний из них создает пакет службы интеграции. О взаимодействии практически любых множеств данных любого размера речь пойдет в главе 42.

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

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

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

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

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

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

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

В файле СН 16-Modifying Data, sql, который можно загрузить с сайта www.SQLServerBible.com, содержатся все примеры программного кода, приводимые в настоящей главе. Дополнительные примеры инструкций модификации данных вы можете найти в сценариях заполнения учебных баз данных, а также в хранимых процедурах базы данных OBXKites.

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

USE СНА2

INSERT INTO dbo.Guide (LastName, FirstName, Qualifications)

VALUES (‘Smith1, ‘Dan’, ‘Diver, Whitewater Rafting’)

INSERT INTO dbo.Guide (FirstName, LastName, Qualifications)

VALUES (‘Jeff’, ‘Davis’, ‘Marine Biologist, Diver’)

INSERT INTO dbo.Guide (FirstName, LastName)

VALUES (‘Tammie’, ‘Commer’)

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

SELECT * FROM dbo.Guide

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

GuidelD LastName FirstName Qualifications

1  Smith            Dan                Diver, Whitewater Rafting

2  Davis            Jeff                Marine Biologist, Diver

3  Commer        Tammie           NULL

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

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

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

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

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

INSERT Guide

VALUES (‘Jones’, ‘Lauren’,

‘First Aid, Rescue/Extraction’, ’25/6/59′, ’15/4/01′)

Чтобы посмотреть на результат операции вставки, извлечем данные из таблицы Guide с помощью инструкции SELECT:

GuidelD LastName FirstName Qualifications

1  Smith           Dan                Diver, Whitewater Rafting

2  Davis           Jeff                Marine Biologist, Diver

3  Commer       Tammie           NULL

4  Jones           Lauren            First Aid, Rescue/Extraction

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

INSERT dbo.Guide (FirstName, LastName, Qualifications)

VALUES (‘Greg’, ‘Wilson’,

‘Rock Climbing’ + ‘,’ + ‘First Aid’)

Следующая инструкция SELECT проверяет вставку данных об инструкторе Greg:

Select * FROM dbo.Guide

Результат будет следующим:

GuidelD LastName FirstName Qualifications

1  Smith           Dan                Diver, Whitewater Rafting

2  Davis           Jeff                Marine Biologist, Diver

3  Commer       Tammie           NULL

4  Jones           Lauren            First Aid, Rescue/Extraction

5  Wilson         Greg               Rock Climbing, First Aid (5 row(s) affected)

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

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

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

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

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

Дополнительная Исчерпывающую дискуссию о предложении select этой инструкции вы найде- ,информация те в главе 7 и последующих главах части II.

Как и в инструкции INSERT. . .VALUES, столбцы должны быть упорядочены и иметь соответствующие типы данных. Если необязательный список столбцов опускается, все столбцы должны заполняться в том порядке, в котором объявлены при создании таблицы (за исключением столбца идентичности).

В следующем примере мы используем базы данных OBXKites. В нем мы извлечем фамилии всех экскурсоводов из таблицы Guide базы данных Cape Hatteras Adventures и вставим их в список клиентов (таблицу Contact) базы данных OBXKites. Имена столбцов извлекаются из таблицы Guide, а название компании мы представляем строковой константой (обратите внимание на то, что при обращении к таблице Guide используется трехчастное имя база_данных. владелец, таблица).

USE OBXKites

— Используем чистую копию базы данных OBXKites, до заполнения

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

FROM CHA2.dbo.Guide

Проверим вставку:

SELECT FirstName AS First, LastName AS Last, CompanyName FROM dbo.Contact

Будет получен следующий результат:

First                 Last    CompanyName

Dan                 Smith  Cape Hatteras Adv.

Jeff                 Davis  Cape Hatteras Adv.

Tammie            Commer          Cape Hatteras Adv.

Lauren             Jones  Cape Hatteras Adv.

Greg                Wilson            Cape Hatteras Adv.

(5 row(s)        affected)

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

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

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

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

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

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

Дополнительная Подробно о программировании хранимых процедур вы узнаете в главе 21.

информация

В следующем примере создается хранимая процедура, возвращающая имена и фамилии сотрудников из баз данных Cape Hatteras Adventures и Northwind (последняя входит в комплект поставки версии SQL Server 2000). После этого создается таблица, в которую будут помещены результирующие наборы данных. После того как будут созданы и хранимая процедура, и место помещения данных, выполняется инструкция INSERT. . . EXEC:

Use СНА2

CREATE PROC ListGuides AS

SET NOCOUNT ON — Результирующий набор данных 1 SELECT FirstName, LastName FROM dbo.Guide — Результирующий набор данных 2 SELECT FirstName, LastName FROM northwind.dbo.employees RETURN

Проверим результаты выполнения хранимой процедуры:

Exec ListGuides

FirstName LastName

Dan    Smith

Jeff    Davis

Tammie          Commer

Lauren           Jones

Wilson           Greg

FirstName LastName

Nancy            Davolio

Andrew          Fuller

Janet  Leverling

Margaret        Peacock

Steven            Buchanan

Michael          Suyama

Robert            King

Laura Callahan

Anne  Dodsworth

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

CREATE TABLE dbo.GuideSample (FirstName VARCHAR(50),

LastName VARCHAR(50) )

Итак, у нас все готово для выполнения инструкции вставки:

INSERT dbo.GuideSample (FirstName, LastName)

EXEC ListGuides

Теперь проверим содержимое новой таблицы с помощью инструкции SELECT:

SELECT * FROM dbo.GuideSample

Будет получен следующий результат:

FirstName LastName

Dan    Smith

Jeff    Davis

Tammie          Commer

Lauren           Jones

Wilson           Greg

Nancy            Davolio

Andrew          Fuller

Janet  Leverling

Margaret        Peacock

Steven            Buchanan

Michael          Suyama

Robert            King

Laura Callahan

Anne  Dodsworth

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

Формы insert . . . ехес и select . . . into не вставляют данные в табличные Внимание! переменные. О табличных переменных мы подробно поговорим в главе 18.

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

Язык 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 заноситься в журнал транзакций не будет.

Дополнительная Подробно об операциях bulk INSERT и BULK COPY вы узнаете в главе 19; информация а о моделях восстановления — в главе 36.

В следующем примере продемонстрировано использование инструкции SELECT. . . INTO для создания новой таблицы GuideList на основе извлечения данных из таблицы Guide (результаты операции несколько усечены):

USE СНА2

— установка режима массового заполнения

Alter DATABASE СНА2 SET RECOVERY FULL

SP_DBOPTION ‘CHA2′, ‘select into/bulkcopy’, ‘TRUE’

— инструкция SELECT… INTO SELECT *

INTO dbo.GuideList FROM dbo.Guide

ORDER BY Lastname, FirstName

Команда sp_help поможет нам увидеть структуру новой таблицы (результаты также несколько усечены):

sp_help GuideList

Будет получен следующий результат (некоторые строки опущены):

Name  Owner Type                       Created_datetime

GuideList dbo      user table 2001-08-01 16:30:02.937

Column_name                   Type          Length Prec Scale Nullable

GuidelD       int                4                                  10 0 no

LastName     varchar         50                                        no

FirstName    varchar         50                                        no

Qualifications                  varchar                              2048      yes

DateOfBirth datetime        8                                         yes

DateHire      datetime        8                                         yes

Identity    Seed          Increment Not For Replication

GuidelD    11                                   0

RowGuidCol

Data_located_on_filegroup PRIMARY

The object does not have any indexes.

No constraints have been defined for this object.

No foreign keys reference this table.

No views with schema binding reference this table.

Следующая инструкция вставляет новую строку в таблицу, созданную инструкцией SELECT. . . INTO для проверки работоспособности столбца идентичности:

INSERT Guidelist (LastName, FirstName, Qualifications)

VALUES(‘Nielsen•, ‘Paul’, ‘trainer’)

Теперь посмотрим на данные, вставленные инструкцией SELECT. . . INTO и только что добавленные инструкцией INSERT. . .VALUES:

SELECT GuidelD, LastName, FirstName FROM dbo.GuideList

GuidelD                     LastName          FirstName

12     Nielsen             Paul

7        Atlas                Sue

11     Bistier              Arnold

3          Commer          Tammie

2          Davis              Jeff

10        Fletcher           Bill

5          Greg                 Wilson

4          Jones                Lauren

1          Smith Dan

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

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

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

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

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

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

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

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

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

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

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии