SQL.RU | INSERT + ORDER BY
Существует очень раcпространенный миф, что конструкция insert into … select … from … order by … гарантирует последовательность вставки данных в таблицу согласно условию order by. Но это заблуждение, т.к. мы не можем гарантировать последовательность физической вставки данных. Сиквел сам (по своему, неведанному нам сценарию) определяет, как данные попадут в таблицу, последовательно или параллельно и какими кусками определяет оптимизатор. При этом кляуза (clause) просто игнорируется.
Но существует очень интересная особенность, когда в таблице, в которую осуществляется вставка, есть поле IDENTITY
Если перенос данных осуществлять через конструкцию SELECT … INTO:
SELECT Col1, Col2, ID=IDENTITY (int, 1, 1) INTO NewTable FROM OldTable Order By Col1
То данные могут расположиться в новой таблице в совершенно произвольном порядке, например:
Col1 Col2 ID ------- -------- -------- 1 A 4 2 Z 2 7 G 5 11 F 3 17 I 1
Однако, если вставку делать в таблицу, которая создана заранее и содержит поле IDENTITY, то конструкция:
INSERT INTO NewTable (Col1, Col2) SELECT Col1, Col2 FROM OldTable ORDER BY Col1
Гарантирует, что данные в таблице расположатся в нужном порядке:
ID (identity) Col1 Col2 ------------- ------ ------ 1 1 S 2 2 z 3 7 G 4 11 F 5 17 I
При этом мы не повлияли на физический порядок вставки данных, дело в том, что значения для поля IDENTITY были созданы до самой вставки и их порядок соответствует условию ORDER BY.
К сожалению в BOL описано лишь это:
Использование предложения ORDER BY с инструкцией SELECT…INTO для вставки строк из другого источника,
не гарантирует вставку строк в указанном порядке.
В остальном официальный хелп молчит, хотя подобные вопросы очень часто поднимаются, как на наших форумах по MS SQL Server, так и на англоязычных…
Эта ситуация признана багом и была «fixed in SQL 2008». Но не отчаивайтесь, если у вас SQL Server 2000 или 2005, т.к. эту особенность можно обойти, если указать в SELECT предложение TOP c числом заведомо больше, чем данных в таблице-источнике.
INSERT INTO RemoteServer.TestDB.SchemaName.NewTable (Col1, Col2) SELECT TOP 1000000000 Col1, Col2 FROM OldTable ORDER BY Col1
Тем самым порядок следования перенесенных данных будет такой же, как при вставке в локальную таблицу.
Ссылки по теме:
http://support.microsoft.com/kb/273586/en-us/
http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx
http://sites.google.com/site/venkatrajagopal/sqlblog/msftsupportresponsetoinsertintoselectissue
Вставка записей в таблицу с помощью SQL — Data to Fish
12 февраля 2022 г.
Вот общий синтаксис, который можно использовать для вставки записей в таблицу с помощью SQL:
INSERT INTO имя_таблицы (столбец_1, столбец_2, столбец_3,...)
ЦЕННОСТИ
('значение_1', 'значение_2', 'значение_3',...)
Теперь давайте рассмотрим пример, чтобы увидеть, как применять приведенный выше синтаксис на практике.
Пример
Предположим, вы создали пустая таблица , называемая « product », которая содержит 3 столбца: product_id , product_name , и price :
CREATE TABLE product ( product_id int первичный ключ, product_name nvarchar(50), цена инт )
Конечная цель — вставить в таблицу следующие 4 записи:
| product_id | имя_продукта | цена |
| 1 | Компьютер | 800 |
| 2 | ТВ | 1200 |
| 3 | Принтер | 150 |
| 4 | Стол | 400 |
Вставка записей в таблицу с помощью SQL
Приведенный ниже запрос можно использовать для вставки 4 записей в таблицу «продукт» (обратите внимание, что вы не должны ставить запятую после закрывающей скобки последнего значения):
ВСТАВИТЬ В продукт (product_id, product_name, цена) ЦЕННОСТИ (1, «Компьютер», 800), (2,'ТВ',1200), (3, "Принтер", 150), (4, «Рабочий стол», 400)
После выполнения вышеуказанного запроса 4 записи будут вставлены в таблицу «продукт».
Чтобы убедиться, что записи были добавлены в таблицу, запустим следующий запрос SELECT:
SELECT * FROM product
Теперь вы увидите новые вставленные записи:
| product_id | имя_продукта | цена |
| 1 | Компьютер | 800 |
| 2 | ТВ | 1200 |
| 3 | Принтер | 150 |
| 4 | Стол | 400 |
Вставка дополнительных записей в таблицу
Вы можете в любое время вставить в таблицу дополнительные записи, используя тот же метод, который рассматривался выше.
Например, добавим две дополнительные записи в таблицу product:
INSERT INTO product (product_id, product_name, price) ЦЕННОСТИ (5, «Стул», 120), (6, «Планшет», 300)
После выполнения запроса две дополнительные записи будут вставлены в таблицу «продукт».
Повторим запрос SELECT:
SELECT * FROM product
Вы увидите, что две дополнительные записи действительно были вставлены внизу таблицы:
| product_id | имя_продукта | цена |
| 1 | Компьютер | 800 |
| 2 | ТВ | 1200 |
| 3 | Принтер | 150 |
| 4 | Стол | 400 |
| 5 | Стул | 120 |
| 6 | Планшет | 300 |
Вы также можете посетить следующую страницу для получения дополнительных руководств по SQL.
Оператор SQL INSERT (Transact SQL)
Оператор INSERT используется для добавления строк в таблицу данных SQL Server. В этой статье мы рассмотрим, как использовать оператор INSERT. Мы обсудим лучшие практики, ограничения и подведение итогов на нескольких примерах.
Это вторая статья из серии статей. Во-первых, это введение в операторы модификации данных SQL Server.
Все примеры для этого урока основаны на Microsoft SQL Server Management Studio и базе данных AdventureWorks2012. Правильно ли вы приступили к работе с SQL Server
Содержание
- Перед тем, как мы начнем
- Базовая структура оператора SQL INSERT
- SQL INSERT INTO — вставка одной строки
- Использование вставки: рекомендации и рекомендации
- Рекомендации по типам данных (заполнение)
- Обработка ошибок
- INSERT SELECT и уникальные идентификаторы
- Свойство столбца идентификаторов
- Значения по умолчанию и другое
- Вставка данных из других таблиц
Прежде чем мы начнем
Хотя в этой статье в качестве примеров используется база данных Adventure Works, я решил создать таблицу примеров для использования в базе данных, чтобы лучше проиллюстрировать примеры.
Основная цель оператора INSERT — добавить строки в таблицу. Хотя оператор вставки вставляет данные из многих источников, таких как литеральные значения или исходные значения, основной формат остается тем же.
В инструкции SQL INSERT :
есть три компонента.- Таблица получает новые строки.
- Столбцы для заполнения
- Исходные данные для новых строк.
Общий формат заявления о вставке:
ВСТАВИТЬ В имя_таблицы
(столбец1, столбец2, …)
ЗНАЧЕНИЯ (значение1, значение2, …)
Теперь мы собираемся сделать несколько примеров вставок, поэтому, если вы еще этого не сделали, запустите сценарий для создания таблицы esqlSalesPerson.
В этом примере мы вставляем одну строку в таблицу esqlSalesPerson. Вот его структура таблицы:
Предположим, мы хотим добавить в таблицу нового продавца. Команда для запуска:
ВСТАВЬТЕ В dbo.esqlSalesPerson
(FullName, SalesLastYear,
City, rowguid)
ЗНАЧЕНИЯ («Джордж Хитсон», NULL,
«Мидленд», «794310D2-6293-4259-AC11-71D96689A3DD»)
esqlSalesPerson Обратите внимание, что мы не указали SalesPersonID в списке столбцов. Это столбец идентификаторов, который заполняется автоматически.
Вы можете переключаться между столбцами; они не должны быть в порядке. Также обратите внимание, что указаны не все столбцы:
ВСТАВЬТЕ В dbo.esqlSalesPerson
(City, FullName,
rowguid)
ЗНАЧЕНИЯ («Traverse City», «Donald Sax»,
'F6E26EFD-5838-40F8-ABB3-D487D2932873')
Создает следующую строку:
Обратите внимание, что, поскольку SalesLastYear не был указан, он равен NULL:
Заключите значения столбца в круглые скобки. Это представляет собой одну строку данных. Чтобы вставить более одной строки, просто включите другой набор значений столбца. Просто не забудьте разделить каждый набор запятой, как показано ниже:
ВСТАВИТЬ В dbo.
ЗНАЧЕНИЯ ( 'Бэй Сити', 'Ральф Гиттер',
'DED7DB59-7149-47DD-8D8F-D5FCFFF11124' ),
( 'Алпена', 'Майк Оранж',
'946003-9ACE-9ACE 8CD727A2C83E' )
esqlSalesPerson (City, FullName, rowguid) Прежде чем перейти к более сложному примеру, важно сделать шаг назад и рассмотреть поведение инструкции 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.FirstName + ' ' + P.LastName, S.SalesLastYear, NEWID()
FROM Sales.SalesPerson S
INNER JOIN Person.Person P
ON P. BusinessEntityID = S.BusinessEntityID
ГДЕ S.SalesLastYear > 1000000 )
ВСТАВИТЬ В esqlSalesPerson
(FullName, SalesLastYear, rowguid)
ВЫБРАТЬ FullName, SalesLastYear, rowguid
ИЗ topSalesPerson
Хотя печатать больше, мне нравится метод CTE. Я думаю, что это облегчает чтение инструкции INSERT.
Помните, что при использовании инструкции SELECT для вставки данных в другую таблицу рекомендуется сначала просто запустить инструкцию SELECT, чтобы убедиться, что вы выбираете правильные строки.