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

Мова SQL пропонує чотири форми інструкцій INSERT і SELECT INTO як основні методи вставки даних (табл 161) У той час як прості методи реалізують вставку всього одного рядка даних, складніші отримують результати від вкладених операторів SELECT і створюють з результатів таблиці

Таблиця 161 Форми операцій вставки

Форма вставки

Опис

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, який можна завантажити з сайту wwwSQLServerBiblecom, містяться всі приклади програмного коду, що наводяться в цій главі Додаткові приклади інструкцій модифікації даних ви можете знайти в сценаріях заповнення навчальних баз даних, а також у збережених процедурах бази даних OBXKites

Наступні інструкції INSERT звертаються до стовпців в різному порядку:

USE СНА2

INSERT INTO dboGuide (LastName, FirstName, Qualifications)

VALUES (Smith1, Dan, Diver, Whitewater Rafting)

INSERT INTO dboGuide (FirstName, LastName, Qualifications)

VALUES (Jeff, Davis, Marine Biologist, Diver)

INSERT INTO dboGuide (FirstName, LastName)

VALUES (Tammie, Commer)

За допомогою наступної інструкції SELECT ми перевіримо правильність вставки:

SELECT * FROM dboGuide

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

GuidelD LastName FirstName Qualifications

1&nbsp Smith            Dan                Diver, Whitewater Rafting

2&nbsp Davis            Jeff                Marine Biologist, Diver

3&nbsp 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&nbsp Smith           Dan                Diver, Whitewater Rafting

2&nbsp Davis           Jeff                Marine Biologist, Diver

3&nbsp Commer       Tammie           NULL

4&nbsp Jones           Lauren            First Aid, Rescue/Extraction

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

INSERT dboGuide (FirstName, LastName, Qualifications)

VALUES (Greg, Wilson,

‘Rock Climbing + , + First Aid)

Наступна інструкція SELECT перевіряє вставку даних про інструктора Greg:

Select * FROM dboGuide

Результат буде наступним:

GuidelD LastName FirstName Qualifications

1&nbsp Smith           Dan                Diver, Whitewater Rafting

2&nbsp Davis           Jeff                Marine Biologist, Diver

3&nbsp Commer       Tammie           NULL

4&nbsp Jones           Lauren            First Aid, Rescue/Extraction

5&nbsp 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 dboContact (FirstName, ContactCode, LastName, CompanyName) SELECT FirstName, LastName, GuidelD, Cape Hatteras Adv’

FROM CHA2dboGuide

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

SELECT FirstName AS First, LastName AS Last, CompanyName FROM dboContact

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

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 dboGuide – Результуючий набір даних 2 SELECT FirstName, LastName FROM northwinddboemployees 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 dboGuideSample (FirstName VARCHAR(50),

LastName VARCHAR(50) )

Отже, у нас все готово для виконання інструкції вставки:

INSERT dboGuideSample (FirstName, LastName)

EXEC ListGuides

Тепер перевіримо вміст нової таблиці за допомогою інструкції SELECT:

SELECT * FROM dboGuideSample

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

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 dboGuideList FROM dboGuide

ORDER BY Lastname, FirstName

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

sp_help GuideList

Буде отримано наступний результат (деякі рядки опущені):

Name  Owner Type                       Created_datetime

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

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 dboGuideList

GuidelD                     LastName          FirstName

12&nbsp&nbsp&nbsp&nbsp Nielsen             Paul

7&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Atlas                Sue

11&nbsp&nbsp&nbsp&nbsp Bistier              Arnold

3&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Commer          Tammie

2&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Davis              Jeff

10&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Fletcher           Bill

5&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Greg                 Wilson

4&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Jones                Lauren

1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 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 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*