Values insert into sql пример: INSERT | SQL | SQL-tutorial.ru

Содержание

Оператор INSERT в SQL, примеры

Здравствуйте, уважаемые читатели. Продолжаем изучать запросы на языке SQL, и сегодня мы затронем еще один важный оператор — INSERT SQL. Разберем примеры, связанные с этим оператором.

Введение

Напомним, что в прошлый раз мы создали базу данных и три таблицы с помощью оператора CREATE, подробнее вы можете почитать об этом здесь.

Таблицы, которые были созданы (Salespeople (Продавцы), Customers (Заказчики), Orders (Заказы)) пока что не содержат никаких данных. И, конечно, следует заполнить их данными, подходящими для каждой таблицы в отдельности.

Общая информация об INSERT в SQL

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

INSERT INTO имя_таблицы (список_столбцов_для_вставки) VALUES (список_вставляемых_значений)

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

INSERT INTO salespeople (snum, sname) VALUES (1001, "Колованов", "Москва")

то система, в которой выполняется этот SQL запрос, выдаст ошибку.

Примеры с оператором INSERT

Для начала мы выполним SQL запросы, которые добавят данные в наши таблицы, чтобы в следующих статьях мы могли к ним обращаться и каким либо образом взаимодействовать. Начнем с таблицы salespeople, у которой 4 столбца:

INSERT INTO salespeople (snum, sname, city, comm) VALUES
(1, "Колованов", "Москва", 10),
(2, "Петров", "Тверь", 25),
(3, "Плотников", "Москва", 22),
(4, "Кучеров", "Санкт-Петербург", 28),
(5, "Малкин", "Санкт-Петербург", 18),
(6, "Шипачев", "Челябинск", 30),
(7, "Мозякин", "Одинцово", 25),
(8, "Проворов", "Москва", 25)

Примечание: чтобы понять, правильно ли сработал запрос, нужно вывести данные таблицы. Это делается с помощью оператора SQL — SELECT (это тема следующих статей, поэтому здесь разберем кратко). Выполняем запрос:

SELECT * FROM salespeople

И получаем такую таблицу с заполненными данными:

snum	sname	city	comm
1	Колованов	Москва	10
2	Петров	Тверь	25
3	Плотников	Москва	22
4	Кучеров	Санкт-Петербург	28
5	Малкин	Санкт-Петербург	18
6	Шипачев	Челябинск	30
7	Мозякин	Одинцово	25
8	Проворов	Москва	25

Итак, далее заполним остальные таблицы:

INSERT INTO customers (cnum, cname, city, rating, snum) VALUES
(1, "Деснов", "Москва", 90, 6),
(2, "Краснов", "Москва", 95, 7),
(3, "Кириллов", "Тверь", 96, 3),
(4, "Ермолаев", "Обнинск", 98, 3),
(5, "Колесников", "Серпухов", 98, 5),
(6, "Пушкин", "Челябинск", 90, 4),
(7, "Лермонтов", "Одинцово", 85, 1),
(8, "Белый", "Москва", 89, 3),
(9, "Чудинов", "Москва", 96, 2),
(10, "Лосев", "Одинцово", 93, 8)

INSERT INTO orders (onum, amt, odate, cnum, snum) VALUES
(1001, 128, '2016-01-01', 9, 4),
(1002, 1800, '2016-04-10', 10, 7),
(1003, 348, '2017-04-08', 2, 1),
(1004, 500, '2016-06-07', 3, 3),
(1005, 499, '2017-12-04', 5, 4),
(1006, 320, '2016-03-03', 5, 4),
(1007, 80, '2017-09-02', 7, 1),
(1008, 780, '2016-03-07', 1, 3),
(1009, 560, '2017-10-07', 3, 7),
(1010, 900, '2016-01-08', 6, 8)

На всякий случай напомню, что в таблице orders(заказы) колонка amt означает стоимость заказа, cnum — номер покупателя, snum — номер продавца, оформившего сделку.

Дополнительная информация

Если вы ввели неправильные данные в таблицу, то всю таблицу можно очистить с помощью SQL оператора TRUNCATE:

TRUNCATE TABLE orders

Если же вы хотите удалить определенную строку, то нужно воспользоваться оператором DELETE, а также указать какую то информацию, описывающую данную строку, например в salespeople это будет snum:

DELETE FROM salespeople WHERE snum = 1

При таком запросе удалится одна строка. В принципе можно удалять несколько строк, если в WHERE указать условие, подходящее для нескольких строк.

Заключение

Итак, мы познакомились с оператором INSERT языка SQL, который вставляет данные в таблицы, на примерах сами научились это делать. На этом все, если у вас остались вопросы, то оставляйте их в комментариях.

2.27. Многие ко многим — Transact-SQL В подлиннике : Персональный сайт Михаила Флёнова

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

Листинг 2.16. Код создания трех таблиц со связью многие ко многим


-- Создаем таблицу фамилий
CREATE TABLE Names
(
 idName uniqueidentifier DEFAULT NEWID(), 
 vcName varchar(50),
 vcLastName varchar(50),
 vcSurName varchar(50),
 CONSTRAINT PK_guid PRIMARY KEY (idName)
)

-- Создаем таблицу телефонов
CREATE TABLE Phones
(
 idPhone uniqueidentifier DEFAULT NEWID(),
 vcPhone varchar(10), 
 CONSTRAINT PK_idPhone PRIMARY KEY (idPhone)
)

-- Создаем связующую таблицу
CREATE TABLE LinkTable
(
 idLinkTable uniqueidentifier DEFAULT NEWID(),
 idName uniqueidentifier, 
 idPhone uniqueidentifier, 

 CONSTRAINT PK_idLinkTable PRIMARY KEY (idLinkTable),

 CONSTRAINT FK_idPhone FOREIGN KEY (idPhone)
   REFERENCES Phones (idPhone),
 CONSTRAINT FK_idName FOREIGN KEY (idName)
   REFERENCES Names (idName)
)

Это немного модифицированный пример того, что мы рассматривали в разделе 1. 2.6. В качестве ключевого поля используется уникальный идентификатор GUID.

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

Листинг 2.17. Добавление записи в три связанных таблицы


-- Вставляем записи в связанные таблицы
DECLARE @ID1 uniqueidentifier
DECLARE @ID2 uniqueidentifier

-- Генерируем идентификаторы
SET @ID1=NEWID()
SET @ID2=NEWID()

-- Вставить жителя
INSERT INTO Names 
VALUES(@ID1, 'ИВАНОВ', 'ИВАН', 'ИВАНОВИЧ')

-- Вставить в таблицу телефонов
INSERT INTO Phones 
VALUES(@ID2, '39927321')

-- Вставить строку в связующую таблицу
INSERT INTO LinkTable (idName, idPhone) 
VALUES(@ID1, @ID2)

Сначала мы объявляем две переменные @ID1 и @ID2 типа uniqueidentifier, которые будут хранить ключи для добавляемых записей. Следующим этапом генерируем эти самые ключи. Теперь можно добавлять записи во все три таблицы.

Теперь давайте посмотрим, как работает эта связь и отобразим фамилию и соответствующий номер телефона:


SELECT vcName, vcPhone 
FROM Names n, Phones p, LinkTable l
WHERE n.idName=l.idName AND p.idPhone=l.idPhone

В результате вы должны увидеть таблицу из двух полей:


vcName                   vcPhone    
------------------------ ---------- 
ИВАНОВ                   39927321

(1 row(s) affected)

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

Посмотрим на код из листинга 2.18. Здесь мы наполняем таблицы и устанавливаем связь без предварительной генерации уникального идентификатора для первичного ключа.

Листинг 2.18. Наполнение таблицы значениями


-- Заполняем фамилиями
INSERT INTO Names (vcName) VALUES('ИВАНОВ')
INSERT INTO Names (vcName) VALUES('ПЕТРОВ')
INSERT INTO Names (vcName) VALUES('СИДОРОВ')

-- Заполняем номерами телефонов
INSERT INTO Phones (vcPhone) VALUES('12387264')
INSERT INTO Phones (vcPhone) VALUES('45435345')
INSERT INTO Phones (vcPhone) VALUES('89349234')

-- Наводим связь 1
DECLARE @ID1 uniqueidentifier
DECLARE @ID2 uniqueidentifier

SELECT @ID1=idName FROM Names WHERE vcName='ИВАНОВ'
SELECT @ID2=idPhone FROM Phones WHERE vcPhone='12387264'

INSERT INTO LinkTable (idName, idPhone) 
VALUES(@ID1, @ID2)
...
. ..

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

Предыдущая глава

2.26. Математические функции

Следующая глава

3.1. Представления View

Назад к содержимому «Transact-SQL В подлиннике»

Оператор SQL INSERT (Transact SQL)

Оператор INSERT используется для добавления строк в таблицу данных SQL Server. В этой статье мы рассмотрим, как использовать оператор INSERT. Мы обсудим лучшие практики, ограничения и подведение итогов на нескольких примерах.

Это вторая статья из серии статей. Во-первых, это введение в операторы модификации данных SQL Server.

Все примеры для этого урока основаны на Microsoft SQL Server Management Studio и базе данных AdventureWorks2012. тебе Начало работы Правильное использование SQL Server ? Если нет, сделайте это с помощью бесплатных инструментов, используя мое бесплатное руководство.

Содержание

Прежде чем мы начнем
Базовая структура оператора SQL INSERT
SQL INSERT INTO — вставка одной строки
Использование вставки: рекомендации и рекомендации SELECT и уникальные идентификаторы
Свойство столбца идентификаторов
Значения по умолчанию и прочее

Вставка данных из других таблиц

Прежде чем мы начнем

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

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

В операторе SQL INSERT :

есть три компонента.

Таблица получает новые строки.
Столбцы для заполнения
Исходные данные для новых строк.

Общий формат заявления о вставке:

 INSERT INTO tableName 
 (column1, column2, …) 
 VALUES (value1, value2, …)

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

В этом примере мы вставляем одну строку в таблицу esqlSalesPerson. Вот его структура таблицы:

Предположим, мы хотим добавить в таблицу нового продавца. Команда для запуска:

 INSERT INTO dbo.esqlSalesPerson 
 (FullName, SalesLastYear, 
 City, rowguid) 
 VALUES («Джордж Хитсон», NULL, 
 «Midland», «794310D2-6293-4259-AC11-71D96689A305»)
  Обратите внимание, что мы не указали SalesPersonID в списке столбцов. Это столбец идентификаторов, который заполняется автоматически. 
  Вы можете переключаться между столбцами; они не должны быть в порядке. Также обратите внимание, что указаны не все столбцы: 
  ВСТАВИТЬ В dbo. esqlSalesPerson 
 (City, FullName, 
 rowguid) 
 ЗНАЧЕНИЯ («Traverse City», «Donald Sax», 
 «F6E26EFD-5838-40F8-ABB3-D487D2932873») 
     Создает следующую строку:
 Обратите внимание, что, поскольку SalesLastYear не был указан, он равен NULL:
 Заключите значения столбца в круглые скобки. Это представляет собой одну строку данных. Чтобы вставить более одной строки, просто включите другой набор значений столбца. Просто не забудьте разделить каждый набор запятой, как показано ниже:
 INSERT INTO dbo.esqlSalesPerson (City, FullName, rowguid) 
 VALUES        (  'Bay City', 'Ralph Gitter', 
 'DED7DB59-7149-47DD-8D8F-D5FCFFF11124'  ),   
               (  'Alpena ', 'Майк Оранж', 
 '94600A1E-DD83-4ACE-9D59-8CD727A2C83E'  )  
 Прежде чем перейти к более сложному примеру, важно сделать шаг назад и рассмотреть поведение инструкции SQL INSERT и некоторые рекомендации.
 Использование вставки: рекомендации и рекомендации 
 Рекомендации по типу данных (заполнение) 
 Имейте в виду, что при вставке данных в столбцы с типом данных CHAR, VARCHAR или VARBINARY заполнение или усечение данных зависит от параметра SET ANSI_PADDING.
 Вставка значения «Крис» в поле, определенное как CHAR(10), приводит к значению, дополненному шестью дополнительными пробелами. Вставленное значение:  'Kris      Правило заполнения может сбивать с толку при использовании INSERT INTO, поэтому ознакомьтесь с подробностями в этой статье.
 Обработка ошибок 
 Вы можете обрабатывать ошибки при выполнении инструкции  INSERT INTO  с помощью конструкции TRY…CATCH.
 Существует несколько распространенных причин, по которым оператор INSERT может завершиться ошибкой. Некоторые из распространенных:
  Нарушение уникального ключа  — вы пытаетесь вставить запись в таблицу, что приводит к дублированию значения ключа.
  Нарушение первичного ключа  — вы пытаетесь вставить запись в таблицу, в которой уже есть строка с таким же первичным ключом.
  Нарушение внешнего ключа  — вы пытаетесь вставить строку в «дочернюю» таблицу, но «родительская» не существует.
  Преобразование типа данных  — Вы пытаетесь вставить строку, в которой одно из значений не может быть правильно преобразовано в соответствующий тип данных столбца.
 В этих случаях выполнение инструкции INSERT останавливается, INSERT выдает ошибку и строки в таблицу не вставляются.
 Имейте в виду, что вы можете изменить это поведение «все или ничего» для арифметических ошибок. Рассмотрим ошибку деления на ноль.
 ВСТАВИТЬ В myNumbers (x, y) 
 ЗНАЧЕНИЯ (10/0, 5), 
 (10/5, 2), 
 (10/2, 5) 
 Этот расчет вызывает ошибку, если для параметра SET ARITHABORT установлено значение ON. В этом случае вставка останавливается, строки не вставляются и возникает ошибка.
 Однако, если для SET ARITHABORT установлено значение OFF, а для ANSI_WARNINGS установлено значение OFF, то тот же оператор будет успешно выполнен.
 Если для него установлено значение OFF, в результате ошибки устанавливается значение NULL.
 Например,
 ВЫКЛ. ARITHABORT 
 ВЫКЛ.
  добавляет три строки со значениями .
  При добавлении строк в таблицы важно понимать, что некоторые столбцы требуют специальной обработки. 
  INSERT SELECT и уникальные идентификаторы 
  При добавлении данных в столбец, объявленный с типом uniqueidentifier, используйте функцию NEWID() для создания глобально уникального значения. 
  В качестве примера 
  ВСТАВЬТЕ В dbo.esqlSalesPerson 
 (City, FullName, rowguid) 
 ЗНАЧЕНИЯ («Traverse City», «Donald Sax», NEWID()) 
 Вставляет новую строку в esqlSalesPerson. Если вы запустите команду еще раз, будет добавлена еще одна строка, но значение rowguid будет другим.
 NEWID() генерирует новое значение при каждом вызове.
 Свойство столбца идентификаторов 
 Новое значение создается для столбца всякий раз, когда строка вставляется в таблицу со свойством столбца идентификаторов. Из-за этого, поскольку esqlSalesPerson.SalesPersonID является столбцом идентификаторов, мы не указываем его в нашей инструкции INSERT. Значение идентификатора увеличивается каждый раз при добавлении строки.
 Если вы попытаетесь вставить строку, используя собственное значение, вы получите ошибку.
 Оператор INSERT
 ВСТАВИТЬ В dbo.esqlSalesPerson 
 (SalesPersonID, City, FullName, rowguid) 
 ЗНАЧЕНИЯ (9999, 'Traverse City', 'Donald Sax', NEWID()) 
 Выдает ошибку
 Не удается вставить явное значение для столбца идентификаторов в таблицу «esqlSalesPerson», если для параметра IDENTITY_INSERT установлено значение OFF. 
 Чтобы обойти это, вы можете SET IDENTITY_INSERT ON
 УСТАНОВИТЬ IDENTITY_INSERT esqlSalesPerson ON; 
 ВСТАВИТЬ В dbo.esqlSalesPerson 
 (SalesPersonID, City, FullName, rowguid) 
 ЗНАЧЕНИЯ (9999, 'Traverse City', 'Donald Sax', NEWID()) 
 Выполняется без ошибок.
 Значения по умолчанию и прочее 
 При вставке строк любые неуказанные столбцы получают значение от СУБД; в противном случае строка не загружается.
 СУБД автоматически предоставляет значения для столбцов, если:
 столбец является столбцом IDENTITY (см. выше)
 указано значение по умолчанию и никакое другое значение не указано.
 столбец может принимать значения NULL. Здесь СУБД устанавливает для столбца значение NULL.
 Столбец коммутативен. Здесь используется расчет.
 Если значение не предоставлено оператором, а механизм не может предоставить значение, строка не может быть вставлена. Обычно это происходит, когда значение отсутствует, а столбец НЕ NULL.
 Вставка данных из других таблиц 
 Вы также можете использовать  INSERT INTO SELECT Оператор  для вставки одной или нескольких строк из одной таблицы в другую. Результаты SELECT передаются в INSERT INTO.
 Общая форма
 INSERT INTO targetTable (column1, column2, …) 
 SELECT (column1, column2, …) 
 FROM sourceTable 
 Предположим, что менеджер по продажам Adventure Works хочет создать таблицу SalesPerson и включить в нее только тех продавцов, чей прошлогодний объем продаж превысил 1 000 000 долларов.
 Вставка с внутренним соединением 
 Чтобы заполнить эту таблицу, вы можете запустить следующую команду INSERT SELECT:
 INSERT INTO esqlSalesPerson 
 (FullName, SalesLastYear, rowguid) 
 SELECT P.FirstName + ' ' + P.LastName, S.SalesLastYear, NEWID() 
 FROM Sales.SalesPerson S 
 INNER JOIN Person.Person P 
 ON P.BusinessEntityID = S.BusinessEntityID 
 ГДЕ S.SalesLastYear > 1000000 
 Чтобы это работало правильно, столбцы, возвращаемые оператором SELECT, должны быть в том же порядке, как указано в списке столбцов INSERT. Обратите внимание, что в этом примере rowguid является обязательным полем. Чтобы заполнить это значение, мы используем функцию NEWID().
 Вы также можете использовать обычное табличное выражение для определения вставляемых строк.
 Вставка с CTE 
 Вы также можете написать пример как CTE (Common Table Expression):
  WITH topSalesPerson (FullName, SalesLastYear, rowguid) 
 AS ( 
 SELECT P.