Содержание
- 1 Инструкция INSERT в T-SQL
- 2 Обработка ошибок
- 3 Совместимость
- 4 Ограничения
- 5 Режим ведения журнала
- 6 Безопасность
- 6.1 Разрешения
- 7 Разница между вставкой и обновлением
- 8 Виды вставки
- 8.1 Вставка по значениям
- 8.2 Вставка по запросу
- 9 Исходные данные
- 9.1 Пример 1 – Добавляем новую запись в таблицу с использованием конструктора табличных значений
- 9.2 Пример 2 – Добавляем новые строки в таблицу с использованием запроса SELECT
- 9.3 Пример 3 – Добавляем новые записи в таблицу с использованием хранимой процедуры
- 10 Условия выборки
- 11 Группировка
- 12 Сложение строк
- 13 Несколько таблиц
- 14 Длина строк
- 15 Изменение строк
- 16 1. Добавление целых строк
- 16.1 2. Добавление части строк
- 16.2 3. Добавление отобранных данных
- 16.3 4. Копирование данных из одной таблицы в другую
- 17 Вставка данных только в указанные столбцы
- 17.1 Пример
- 18 SQL INSERT INTO SELECT
- 18.1 Синтаксис INSERT INTO SELECT
- 19 Демо база данных
- 20 Примеры SQL INSERT INTO SELECT
- 20.1 Пример
- 20.2 Пример
- 20.3 Пример
INSERT – это инструкция языка T-SQL, которая предназначена для добавления данных в таблицу, т.е. создания новых записей. Данную инструкцию можно использовать как для добавления одной строки в таблицу, так и для массовой вставки данных. Для выполнения инструкции INSERT требуется разрешение на вставку данных (INSERT) в целевую таблицу.
Существует несколько способов использования инструкции INSERT в части данных, которые необходимо вставить:
- Перечисление конкретных значений для вставки;
- Указание набора данных в виде запроса SELECT;
- Указание набора данных в виде вызова процедуры, которая возвращает табличные данные.
Заметка! Начинающим рекомендую посмотреть мой видеокурс по T-SQL.
Упрощённый синтаксис
INSERT [INTO] [таблица] (список столбцов, …) VALUES (список значений, …) Или SELECT запрос на выборку Или EXECUTE процедура
Где,
- INSERT INTO – это команда добавления данных в таблицу;
- Таблица – это имя целевой таблицы, в которую необходимо вставить новые записи;
- Список столбцов – это перечень имен столбцов таблицы, в которую будут вставлены данные, разделенные запятыми;
- VALUES – это конструктор табличных значений, с помощью которого мы указываем значения, которые будем вставлять в таблицу;
- Список значений – это значения, которые будут вставлены, разделенные запятыми. Они перечисляются в том порядке, в котором указаны столбцы в списке столбцов;
- SELECT – это запрос на выборку данных для вставки в таблицу. Результирующий набор данных, который вернет запрос, должен соответствовать списку столбцов;
- EXECUTE – это вызов процедуры на получение данных для вставки в таблицу. Результирующий набор данных, который вернет хранимая процедура, должен соответствовать списку столбцов.
Вот примерно так и выглядит упрощённый синтаксис инструкции INSERT INTO, в большинстве случаев именно так Вы и будете добавлять новые записи в таблицы.
Список столбцов, в которые Вы будете вставлять данные, можно и не писать, в таком случае их порядок будет определен на основе фактического порядка столбцов в таблице. При этом необходимо помнить этот порядок, когда Вы будете указывать значения для вставки или писать запрос на выборку. Лично я Вам рекомендую все-таки указывать список столбцов, в которые Вы планируете добавлять данные.
Также следует помнить и то, что в списке столбцов и в списке значений, соответственно, должны присутствовать так называемые обязательные столбцы, это те, которые не могут содержать значение NULL. Если их не указать, и при этом у столбца отсутствует значение по умолчанию, будет ошибка.
Еще хотелось бы отметить, что тип данных значений, которые Вы будете вставлять, должен соответствовать типу данных столбца, в который будет вставлено это значение, ну или, хотя бы, поддерживал неявное преобразование. Но я Вам советую контролировать тип данных (формат) значений, как в списке значений, так и в запросе SELECT.
Хватит теории, переходим к практике.
Обработка ошибок
Для инструкции INSERT можно реализовать обработку ошибок, указав инструкцию в конструкции TRY…CATCH.
Если инструкция INSERT нарушает ограничение или правило, либо в ней присутствует значение, несовместимое с типом данных столбца, то при выполнении инструкции происходит сбой и отображается сообщение об ошибке.
Если инструкция INSERT загружает несколько строк с помощью инструкции SELECT или EXECUTE, то любые нарушения правил или ограничений, возникающие из-за загружаемых значений, приводят к остановке выполнения инструкции, и ни одна из строк не будет загружена.
Если при выполнении инструкции INSERT возникает арифметическая ошибка (переполнение, деление на ноль или ошибка домена), компонент Компонент Database Engine обрабатывает эти ошибки так же, как если бы параметру SET ARITHABORT было присвоено значение ON. Выполнение пакета прекращается и выводится сообщение об ошибке. Во время оценки выражения, когда параметры SET ARITHABORT и SET ANSI_WARNINGS установлены в значение OFF, если в инструкции INSERT, DELETE или UPDATE происходит арифметическая ошибка переполнения, деления на ноль или ошибка области определения, SQL Server вставляет или обновляет значение NULL. Если целевой столбец не пустой, вставка или обновление не осуществляются, и пользователь получает ошибку.
Совместимость
Если триггер INSTEAD OF определен в операциях INSERT для таблицы или представления, то триггер выполняется вместо инструкции INSERT. Дополнительные сведения о триггерах INSTEAD OF см. в разделе CREATE TRIGGER (Transact-SQL).
Ограничения
Если во время вставки значений в удаленные таблицы указаны не все значения для всех столбцов, то необходимо указать столбцы, в которые вставляются заданные значения.
При использовании выражения TOP в инструкции INSERT строки, на которые имеются ссылки, не упорядочиваются, а предложение ORDER BY не может быть прямо указано в этих инструкциях. Если для вставки строк в значимом хронологическом порядке необходимо использовать предложение TOP, вместе с ним в инструкции подзапроса выборки следует использовать предложение ORDER BY. См. подраздел «Примеры» далее в этом разделе.
Запросы INSERT, которые используют SELECT с ORDER BY для заполнения строк, гарантируют способ вычисления значений идентификатора, но не порядок вставки строк.
В Parallel Data Warehouse предложение ORDER BY недопустимо в инструкциях VIEWS, CREATE TABLE AS SELECT, INSERT SELECT, встраиваемых функциях, производных таблицах, подзапросах и обобщенных табличных выражениях, если также не указать TOP.
Режим ведения журнала
Инструкция INSERT всегда полностью регистрируется в журнале, кроме случаев использования функции OPENROWSET с ключевым словом BULK или выполнения инструкции INSERT INTO SELECT FROM. Для этих операций возможно минимальное протоколирование. Дополнительные сведения см. в подразделе «Рекомендации по массовой загрузке данных» этого раздела.
Безопасность
При соединении со связанным сервером отправляющий сервер указывает имя входа и пароль для подключения к принимающему серверу от его имени. Для работы этого соединения необходимо создать сопоставление имен входа между связанными серверами вызовом хранимой процедуры sp_addlinkedsrvlogin.
При использовании функции OPENROWSET(BULK…) важно понимать, каким образом SQL Server обрабатывает олицетворение. Дополнительные сведения см. в главе «Вопросы безопасности» в разделе Массовый импорт данных при помощи инструкции BULK INSERT или OPENROWSET(BULK…) (SQL Server).
Разрешения
Требуется разрешение INSERT на целевую таблицу.
Разрешения INSERT предоставлены по умолчанию членам предопределенной роли сервера sysadmin, членам предопределенных ролей баз данных db_owner и db_datawriter, а также владельцу таблицы. Члены ролей sysadmin, db_owner и db_securityadmin, а также владелец таблицы могут передавать разрешения другим пользователям.
Чтобы выполнить инструкцию INSERT с параметром BULK функции OPENROWSET, необходимо быть членом предопределенной роли сервера sysadmin или bulkadmin.
Разница между вставкой и обновлением
Этот раздел также можно назвать, или альтернативные заголовки раздела:
- Разница между INSERT и UPDATE
SQL стейтмент INSERT INTO только вставляет новые записи (строки, ряды) в существующую таблицу, а не вставляет новые значения в ячейки существующих записей (строк, рядов). Вставкой новых значений в ячейки существующих записей (строк, рядов) в SQL занимается оператор обновления UPDATE .
Виды вставки
SQL стейтмент INSERT INTO поддерживает вставку данных в двух видах: вставка данных по значениям и вставка по выборке.
Вставка по значениям
Вставка по значениям предусматривает следующий синтаксис INSERT … VALUES. Производить вставки таким образом можно в двух формах: полная и сокращённая. При полной форме помимо самого названия таблицы так же указываются столбцы таблицы в которые вставляются данные, при сокращенной столбцы не указывается. Для каждой из форм доступна множественная вставка, при которой ряды со значениями (круглые скобки) перечисляются через запятую.
Вставка по запросу
Вставка по запросу предусматривает след. синтаксис: INSERT … SELECT. Т.е. сначала данные вытаскиваются из одной таблицы, а затем они вставляются в др. таблицу. При этом должны быть соблюдены след. условия:
- количество значений в строке (число столбцов) полученной по запросу должно совпадать с количеством столбцов при вставке;
- типы данных должны совпадать;
- названия столбцов роли не играют.
Исходные данные
Для того чтобы добавлять данные в таблицу, нам нужна соответственно сама таблица, давайте ее создадим, и уже в нее будем пробовать добавлять записи.
Примечание!Все примеры будут выполнены в Microsoft SQL Server 2016 Express.
CREATE TABLE TestTable( [Id] [INT] IDENTITY(1,1) NOT NULL, [ProductName] [VARCHAR](100) NOT NULL, [Price] [Money] NOT NULL )
Наша тестовая таблица, будет содержать перечень товаров с ценой.
Также в примерах мы будем использовать процедуру, которая возвращает табличное значение, для добавления данных в таблицу, поэтому давайте ее тоже создадим.
CREATE PROCEDURE TestProcedure AS BEGIN SELECT ProductName, Price FROM TestTable END
Для примера она у нас будет возвращать данные из только что созданной таблицы TestTable.
Пример 1 – Добавляем новую запись в таблицу с использованием конструктора табличных значений
Сначала давайте попробуем добавить одну запись и сразу посмотрим на результат, т.е. напишем запрос на выборку.
INSERT INTO TestTable(ProductName, Price) VALUES (‘Компьютер’, 100) GO SELECT * FROM TestTable
Вы видите, что мы после названия таблицы перечислили через запятую имена столбцов, в которые мы будем добавлять данные, затем мы указали ключевое слово VALUES и в скобочках также, в том же порядке, через запятую написали значения, которые мы хотим вставить.
После инструкции INSERT я написал инструкцию SELECT и разделил их командой GO.
А теперь давайте представим, что нам нужно добавить несколько строк. Мы для этого напишем следующий запрос.
INSERT INTO TestTable(ProductName, Price) VALUES (‘Компьютер’, 100), (‘Клавиатура’, 20), (‘Монитор’, 50) GO SELECT * FROM TestTable
В данном случае мы добавили три записи, т.е. три строки. После VALUES значения каждой новой строки указаны в скобочках, разделили мы их запятыми.
Пример 2 – Добавляем новые строки в таблицу с использованием запроса SELECT
Очень часто возникает необходимость добавлять много данных в таблицу, например, на основе запроса на выборку, т.е. SELECT. Для этого вместо VALUES нам всего лишь нужно указать запрос.
INSERT INTO TestTable(ProductName, Price) SELECT ProductName, Price FROM TestTable WHERE Id > 2 GO SELECT * FROM TestTable
В данном примере мы написали запрос SELECT, который возвращает данные из таблицы TestTable, но не все, а только те, у которых идентификатор больше 2. А результат вставили все в ту же таблицу TestTable.
В качестве примера того, как можно добавлять записи в таблицу без указания списка столбцов, давайте напишем еще один запрос на вставку данных, который сделает равно то же самое что и запрос выше, только в нем не будет перечисления столбцов для вставки.
INSERT INTO TestTable SELECT ProductName, Price FROM TestTable WHERE Id > 2 GO SELECT * FROM TestTable
В данном случае мы уверены в том, что в таблице TestTable первый столбец это ProductName, а второй Price, поэтому мы можем позволить себе написать именно так. Но, снова повторюсь, на практике лучше указывать список столбцов.
Если Вы заметили, я во всех примерах не указывал столбец Id, а он у нас есть, ошибки не возникло, так как данный столбец со свойством IDENTITY, он автоматически генерирует идентификаторы, поэтому в такой столбец вставить данные просто не получится.
Пример 3 – Добавляем новые записи в таблицу с использованием хранимой процедуры
Сейчас давайте вставим в таблицу данные, которые нам вернёт хранимая процедура. Смысл здесь такой же, вместо VALUES и вместо запроса мы указываем вызов процедуры. Но как Вы понимаете, порядок и количество столбцов, возвращаемых процедурой, должен строго совпадать со списком столбцов для вставки (даже если список столбцов не указан).
INSERT INTO TestTable(ProductName, Price) EXEC TestProcedure GO SELECT * FROM TestTable
Условия выборки
- условие
WHERE - логическое ИЛИ
OR - логическое И
AND - сортировка
ORDER BY - ограничение выборки
LIMIT - выбор из промежутка
BETWEEN - заданные значения
IN - T поиск по шаблону
LIKE - смена имен полей
AS - уникальные значения
DISTINCT - отрицание
NOT - значение не равно NULL
IS NOT NULL - значение равно NULL
IS NULL
Группировка
- группировка
GROUP BY - условие на group by
HAVING
Сложение строк
- сложение строк
CONCAT - сложение с разделителем
CONCAT_WS - сложение ячеек
GROUP_CONCAT
Несколько таблиц
- объединение таблиц
UNION - связывание таблиц
JOIN - левый join
LEFT JOIN - правый join
RIGHT JOIN - внутренний join
INNER JOIN - копирование в другую таблицу
SELECT INTO
Длина строк
- длина строки
LENGTH
Изменение строк
- вырезание слева
LEFT - вырезание справа
RIGHT - взятие подстроки
SUBSTRING - взятие подстроки
MID - взятие подстроки
SUBSTRING_INDEX - поиск и замена
REPLACE - дополнение строки слева
LPAD - дополнение строки справа
RPAD - переворот строки
REVERSE - повторение строки
REPEAT
1. Добавление целых строк
Как видно из названия, оператор INSERT используется для вставки (добавления) строк в таблицу базы данных. Добавление можно осуществить несколькими способами:
- — добавить одну полную строку
- — добавить часть строки
- — добавить результаты запроса.
Итак, чтобы добавить новую строку в таблицу, нам необходимо указать название таблицы, перечислить названия колонок и указать значение для каждой колонки с помощью конструкции INSERT INTO название_таблицы (поле1, поле2 … ) VALUES (значение1, значение2 …). Рассмотрим на примере.
INSERT INTO Sellers (ID, Address, City, Seller_name, Country) VALUES (‘6’, ‘1st Street’, ‘Los Angeles’, ‘Harry Monroe’, ‘USA’)
Также можно изменять порядок указания названий колонок, однако одновременно нужно менять и порядок значений в параметре VALUES.
2. Добавление части строк
В предыдущем примере при использовании оператора INSERT мы явно отмечали имена столбцов таблицы. Используя данный синтаксис, мы можем пропустить некоторые столбцы. Это значит, что вы вводите значение для одних столбцов но не предлагаете их для других. Например:
INSERT INTO Sellers (ID, City, Seller_name) VALUES (‘6’, ‘Los Angeles’, ‘Harry Monroe’)
В данном примере мы не указали значение для двух столбцов Address и Country . Вы можете исключать некоторые столбцы из оператора INSERT INTO, если это позволяет производить определение таблицы. В этом случае должно соблюдаться одно из условий: этот столбец определен как допускающий значение NULL (отсутствие какого-либо значения) или в определение таблицы указанное значение по умолчанию. Это означает, что, если не указано никакое значение, будет использовано значение по умолчанию. Если вы пропускаете столбец таблицы, которая не допускает появления в своих строках значений NULL и не имеет значения, определенного для использования по умолчанию, СУБД выдаст сообщение об ошибке, и это строка не будет добавлена.
3. Добавление отобранных данных
В предыдущей примерах мы вставляли данные в таблицы, прописывая их вручную в запросе. Однако оператор INSERT INTO позволяет автоматизировать этот процесс, если мы хотим вставлять данные из другой таблицы. Для этого в SQL существует такая кострукция как INSERT INTO … SELECT … . Данная конструкция позволяет одновременно выбирать данные из одной таблицы, и вставить их в другую. Предположим мы имеем еще одну таблицу Sellers_EU с перечнем продавцов нашего товара в Европе и нам нужно их добавить в общую таблицу Sellers. Структура этих таблиц одинакова (то же количество колонок и те же их названия), однако другие данные. Для этого мы можем прописать следующий запрос:
INSERT INTO Sellers (ID, Address, City, Seller_name, Country) SELECT ID, Address, City, Seller_name, Country FROM Sellers_EU
Нужно обратить внимание, чтобы значение внутренних ключей не повторялись (поле ID), в противном случае произойдет ошибка. Оператор SELECT также может включать предложения WHERE для фильтрации данных. Также следует отметить, что СУБД не обращает внимания на названия колонок, которые содержатся в операторе SELECT, для нее важно только порядок их расположения. Поэтому данные в первом указанном столбце, что были выбраны из-за SELECT, будут в любом случае заполнены в первый столбец таблицы Sellers, указанной после оператора INSERT INTO, независимо от названия поля.
4. Копирование данных из одной таблицы в другую
Часто при работе с базами данных возникает необходимость в создании копий любых таблиц, с целью резервирования или модификации. Чтобы сделать полную копию таблицы в SQL предусмотрен отдельный оператор SELECT INTO. Например, нам нужно создать копию таблицы Sellers, нужно будет прописать запрос следующим образом:
SELECT * INTO Sellers_new FROM Sellers
В отличие от предыдущей конструкции INSERT INTO … SELECT … , когда данные добавляются в существующую таблицу, конструкция SELECT … INTO … FROM … копирует данные в новую таблицу. Также можно сказать, что первая конструкция импортирует данные, а вторая — экспортирует. При использовании конструкции SELECT … INTO … FROM … следует учитывать следующее:
- — можно использовать любые предложения в операторе SELECT, такие как GROUP BY и HAVING
- — для добавления данных из нескольких таблиц можно использовать объединение
- — данные возможно добавить только одну таблицу, независимо от того, из скольких таблиц они были взяты.
Вставка данных только в указанные столбцы
Кроме того, можно вставлять данные только в определенные столбцы.
Следующая инструкция SQL вставит новую запись, но только вставит данные в столбцы «CustomerName», «City» и «Country» (CustomerID будет обновляться автоматически):
Пример
INSERT INTO Customers (CustomerName, City, Country)
VALUES (‘Cardinal’, ‘Stavanger’, ‘Norway’);
Выбор из таблицы «Customers» теперь будет выглядеть следующим образом:
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
| 89 | White Clover Markets | Karl Jablonski | 305 — 14th Ave. S. Suite 3B | Seattle | 98128 | USA |
| 90 | Wilman Kala | Matti Karttunen | Keskuskatu 45 | Helsinki | 21240 | Finland |
| 91 | Wolski | Zbyszek | ul. Filtrowa 68 | Walla | 01-012 | Poland |
| 92 | Cardinal | null | null | Stavanger | null | Norway |
SQL INSERT INTO SELECT
Инструкция INSERT INTO SELECT копирует данные из одной таблицы и вставляет их в другую таблицу.
- INSERT INTO SELECT требует, чтобы типы данных в исходной и целевой таблицах совпадали
- Существующие записи в целевой таблице остаются неизменными
Синтаксис INSERT INTO SELECT
Копирование всех столбцов из одной таблицы в другую таблицу:
INSERT INTO table2
SELECT * FROM table1
WHERE condition;
Скопируйте только некоторые столбцы из одной таблицы в другую таблицу:
INSERT INTO table2 (column1, column2, column3, …)
SELECT column1, column2, column3, …
FROM table1
WHERE condition;
Демо база данных
В этом уроке мы будем использовать хорошо известный пример базы данных Northwind.
Ниже приведен выбор из таблицы «Customers»:
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
И выбор из таблицы «Suppliers»:
SupplierIDSupplierNameContactNameAddressCityPostal CodeCountry
| 1 | Exotic Liquid | Charlotte Cooper | 49 Gilbert St. | Londona | EC1 4SD | UK |
| 2 | New Orleans Cajun Delights | Shelley Burke | P.O. Box 78934 | New Orleans | 70117 | USA |
| 3 | Grandma Kelly’s Homestead | Regina Murphy | 707 Oxford Rd. | Ann Arbor | 48104 | USA |
Примеры SQL INSERT INTO SELECT
Следующая инструкция SQL копирует «Suppliers» в «Customers» (столбцы, которые не заполнены данными, будут содержать NULL):
Пример
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers; Попробуйте сами »
Следующая инструкция SQL копирует «Suppliers» в «Customers» (заполните все столбцы):
Пример
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers; Попробуйте сами »
Следующая инструкция SQL копирует только немецких поставщиков в раздел «Customers»:
Пример
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country=’Germany’; Попробуйте сами
Источники
- https://info-comp.ru/obucheniest/626-insert-into-in-t-sql.html
- https://docs.microsoft.com/ru-ru/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver15
- https://codewiki.imagetube.xyz/code/SQL/INSERT
- http://old.code.mu/sql/insert.html
- http://moonexcel.com.ua/%D1%83%D1%80%D0%BE%D0%BA%D0%B8-sql13-%D0%B4%D0%BE%D0%B1%D0%B0%D0%B2%D0%BB%D0%B5%D0%BD%D0%B8%D0%B5-%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85-insert-into_ru
- https://schoolsw3.com/sql/sql_insert.php
- https://schoolsw3.com/sql/sql_insert_into_select.php
У меня простая таблица с 6 столбцами. В большинстве случаев любые операторы вставки в него работают нормально, но время от времени я получаю исключение тайм-аута БД:
Истекло время ожидания. Время ожидания истекло до завершения операции или сервер не отвечает. Заявление было прекращено.
Тайм-аут установлен на 10 секунд.
Я должен упомянуть, что я использую NHibernate и что оператор также включает «select SCOPE_IDENTITY ()» сразу после самой вставки.
Я подумал, что таблица была заблокирована или что-то в этом роде, но в то время не было других операторов, работающих на этой таблице.
Все вставки очень простые, в профилировщике sql все выглядит нормально, в таблице нет индексов, кроме PK (заполненность страницы: 98,57%).
Есть идеи, что мне искать?
Спасибо.
Перейти к ответу
Данный вопрос помечен как решенный
Ответы
6
Посмотрите на фрагментацию таблицы, вы можете получить разбиение страницы из-за этого
no other statements running on that table at that time.
А как насчет операторов, выполняемых с другими таблицами как часть транзакции? Это может оставить замки в таблице проблем.
Также проверьте, не происходит ли рост файла журнала или файла данных в то время, если вы используете SQL2005, это будет отображаться в журналах ошибок SQL.
Я думаю, что ваш наиболее вероятный виновник — блокировка от другой транзакции (или, может быть, от триггера или чего-то еще за кулисами).
Самый простой способ узнать об этом — запустить INSERT и, пока он завис, запустить EXEC SP_WHO2 в другом окне на том же сервере. В нем будут перечислены все текущие операции с базой данных и будет столбец BLK, который покажет вам, заблокированы ли какие-либо процессы в данный момент. Проверьте SPID вашего зависшего соединения, чтобы увидеть, есть ли что-нибудь в столбце BLK, и если да, то это процесс, который вас блокирует.
Даже если вы не думаете, что выполняются какие-либо другие операторы, единственный способ узнать наверняка — это перечислить текущие транзакции, используя такой SP.
Может быть, стол долго растет.
Если у вас есть таблица, настроенная на большой рост, и у вас не включена мгновенная инициализация файла, то время от времени запрос, безусловно, может отключаться по таймауту.
Проверьте этот беспорядок: MSDN
У нашего QA было несколько подключений к Excel, которые возвращали большие наборы результатов, эти запросы на некоторое время были приостановлены с WaitType ASYNC_NETWORK_IO. В это время истекло время ожидания всех остальных запросов, так что конкретная вставка не имела к этому никакого отношения.
Этот вопрос кажется хорошим местом для фрагмента кода, который я использовал, чтобы увидеть фактический текст SQL заблокированных и блокирующих запросов.
В приведенном ниже фрагменте используется соглашение, согласно которому SP_WHO2 возвращает «.» текст для BlockedBy для неблокированных запросов, и поэтому он их отфильтровывает и возвращает текст SQL оставшихся запросов (как «жертв», так и «виновников»):
--prepare a table so that we can filter out sp_who2 results
DECLARE @who TABLE(BlockedId INT,
Status VARCHAR(MAX),
LOGIN VARCHAR(MAX),
HostName VARCHAR(MAX),
BlockedById VARCHAR(MAX),
DBName VARCHAR(MAX),
Command VARCHAR(MAX),
CPUTime INT,
DiskIO INT,
LastBatch VARCHAR(MAX),
ProgramName VARCHAR(MAX),
SPID_1 INT,
REQUESTID INT)
INSERT INTO @who EXEC sp_who2
--select the blocked and blocking queries (if any) as SQL text
SELECT
(
SELECT TEXT
FROM sys.dm_exec_sql_text(
(SELECT handle
FROM (
SELECT CAST(sql_handle AS VARBINARY(128)) AS handle
FROM sys.sysprocesses WHERE spid = BlockedId
) query)
)
) AS 'Blocked Query (Victim)',
(
SELECT TEXT
FROM sys.dm_exec_sql_text(
(SELECT handle
FROM (
SELECT CAST(sql_handle AS VARBINARY(128)) AS handle
FROM sys.sysprocesses WHERE spid = BlockedById
) query)
)
) AS 'Blocking Query (Culprit)'
FROM @who
WHERE BlockedById != ' .'
Другие вопросы по теме
INSERT INTO Users (userId, username, password, email, fname, lname, usertype)
Values(1, 'admin','admin','abc@gmail.com','firstname','lastname','admin')
Error Message:
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘Users’.
I’m using windows authentication in Microsoft SQL Server 2012. I was creating tables and inserting data to my previous tables. When I create a new table it doesn’t detect from my SQL statement of inserting values.
Please Help!
Я пытаюсь вставить в БД со следующими столбцами: ID (autonumber), BonderIdentifier (текст), Username (текст), Login (date), Logout (date).
BonderIdentifier, имя пользователя, логин — это ПК.
Вот что я делаю:
Public Function submitNewToDB(ByVal sessionData As BonderSession) As Boolean
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim str As String
Try
cn = New OleDbConnection("Provider=microsoft.Jet.OLEDB.4.0;Data Source=G:SeanBMSBonder3_0.mdb;")
cn.Open()
str = String.Format("Insert into Session ([BonderIdentifier], [Username], [Login]) values ('{0}', '{1}', '{2}')", sessionData.bonderIdentifier _
, sessionData.username, sessionData.login)
cmd = New OleDbCommand(str, cn)
cmd.ExecuteNonQuery()
cn.Close()
Catch ex As Exception
Return False
End Try
Return True
End Function
Как я уже сказал, у меня вставка в ошибку, и я не знаю почему. В БД пока ничего нет, и таблица создана.
ИЗМЕНИТЬ Я запустил встроенную строку в Access как таковую:
Вставить в сеанс ([BonderIdentifier], [Username], [Login]) значения (‘Mork’, ‘sean’, ‘23.02.2010 11:12:42 AM’)
И это работает …. но в VS этого нет.
4 ответа
Лучший ответ
То, что это синтаксическая ошибка, означает, что что-то не так с вашим оператором (str).
Каково точное значение str при возникновении ошибки? Я предполагаю, что это примерно так:
Insert into Session ([BonderIdentifier], [Username], [Login]) values ('Bill O'Brien', 'some text', 'some other text')"
В моем примере проблема с ' в Bill O'Brien. Парсер считает, что ' закрывает текстовое значение, а затем видит больше текста вместо запятой или )
Это также может быть неправильное форматирование данных, которое Access не понимает как дату. Я хотел бы увидеть строку для определения проблемы.
РЕДАКТИРОВАТЬ:
Access требует, чтобы значения даты передавались с окружением #, а не '.
Как это:
Insert into Session ([BonderIdentifier], [Username], [Login]) values ('Bill OBrien', 'some text', #yourdatevalue#)"
1
Gabriel McAdams
23 Фев 2010 в 22:16
При работе с ядром базы данных Access / Jet даты разделяются знаком #, а не знаком '. Попробуйте изменить свое утверждение на:
str = String.Format("Insert into Session ([BonderIdentifier], [Username], [Login]) values ('{0}', '{1}', #{2}#)" _
, sessionData.bonderIdentifier, sessionData.username, sessionData.login)
4
Jeremy S
23 Фев 2010 в 22:16
Я подозреваю, что проблема в том, как дата преобразуется в строку. Можете ли вы опубликовать результат запроса (после подстановки), чтобы мы могли увидеть реальную проблему?
0
D’Arcy Rittich
23 Фев 2010 в 22:14
Я переписал ваш код, чтобы использовать оператор Using и Параметры .
Public Sub SubmitSessionToDB(ByVal sessionData As BonderSession)
Using conn AS New OleDbConnection("Provider=microsoft.Jet.OLEDB.4.0;Data Source=G:SeanBMSBonder3_0.mdb;")
Using cmd AS New OleDbCommand("Insert into Session ([BonderIdentifier], [Username], [Login]) values (@BonderId, @Username, @Login)", conn)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@BonderId", sessionData.bonderIdentifier)
cmd.Parameters.AddWithValue("@Username", sessionData.username)
cmd.Parameters.AddWithValue("@Login", sessionData.login)
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using
End Sub
Я написал его без Visual Studio, поэтому возможны небольшие опечатки.
Оператор using гарантирует, что соединение всегда будет закрыто (даже при возникновении исключения).
Параметры предотвращают внедрение SQL-кода, повышают удобочитаемость и упрощают обнаружение ошибок.
Если вам все еще нужно возвращаемое значение, вы можете добавить полученную попытку.
Пожалуйста, дайте мне знать, если это также решит синтаксическую ошибку.
0
Yvo
23 Фев 2010 в 22:20
Инструкция INSERT INTO SQL и INSERT INTRO SELECT используются для вставки новых строк в таблицу. Существует два способа использования инструкций:
- Только значения: Первый метод предусматривает указание только значений данных, которые нужно вставить без имен столбцов.
Синтаксис:
INSERT INTO имя_таблицы VALUES (значение1, значение2, значение3,...); имя_таблицы: имя таблицы. значение1, значение2,.. : значения первого столбца, второго столбца,... для новой записи
- Имена столбцов и значения: При втором методе указываются имена столбцов и значения строк для вставки:
Синтаксис:
INSERT INTO имя_таблицы (столбец1, столбец2, столбец3,..) VALUES (значение1, значение2, значение3,...); имя_таблицы: имя таблицы. столбец1: имя первого столбцы, второго столбца ... значение1, значение2,.. : значения первого столбца, второго столбца,... для новой записи
Запросы:
Способ 1 (вставка только значений):
INSERT INTO Student VALUES ('5','HARSH','WEST BENGAL','8759770477','19');
Результат:
После использования INSERT INTO SELECT таблица Student теперь будет выглядеть следующим образом:
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 1 | Ram | Delhi | 9455123451 | 18 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 4 | SURESH | Delhi | 9156768971 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 5 | HARSH | WEST BENGAL | 8759770477 | 19 |
Способ 2 (вставка значений только в указанные столбцы):
INSERT INTO Student (ROLL_NO, NAME, Age) VALUES ('5','PRATIK','19');
Результат:
Таблица Student теперь будет выглядеть следующим образом:
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 1 | Ram | Delhi | 9455123451 | 18 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 4 | SURESH | Delhi | 9156768971 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 5 | PRATIK | null | null | 19 |
Обратите внимание, что для столбцов, значения для которых не указаны, задается null.
Использование SELECT в инструкции INSERT INTO
Можно использовать инструкцию MySQL INSERT SELECT для копирования строк из одной таблицы и их вставки в другую.
Использование этого оператора аналогично использованию INSERT INTO. Разница в том, что оператор SELECT применяется для выборки данных из другой таблицы. Ниже приведены различные способы использования INSERT INTO SELECT:
- Вставка всех столбцов таблицы: можно скопировать все данные таблицы и вставить их в другую таблицу.
Синтаксис:
INSERT INTO первая_таблица SELECT * FROM вторая_таблица; первая_таблица: имя первой таблицы. вторая_таблица: имя второй таблицы.
Мы использовали инструкцию SELECT для копирования данных из одной таблицы и инструкцию INSERT INTO для их вставки в другую.
- Вставка отдельных столбцов таблицы. Можно скопировать только те столбцы таблицы, которые необходимо вставить в другую таблицу.
Синтаксис:
INSERT INTO первая_таблица(имена_столбцов1) SELECT имена_столбцов2 FROM вторая_таблица; первая_таблица: имя первой таблицы. вторая_таблица: имя второй таблицы. имена_столбцов1: имена столбцов, разделенные запятой(,) для таблицы 1. имена_столбцов2: имена столбцов, разделенные запятой(,) для таблицы 2.
Мы использовали инструкцию SELECT для копирования данных только из выбранных столбцов второй таблицы и инструкцию INSERT INTO MySQL SELECT для их вставки в первую таблицу.
- Копирование определенных строк из таблицы. Можно скопировать определенные строки из таблицы для последующей вставки в другую таблицу с помощью условия WHERE с оператором SELECT. В этом случае нужно использовать соответствующее условие в WHERE.
Синтаксис:
INSERT INTO таблица1 SELECT * FROM таблица2 WHERE условие; таблица1: имя первой таблицы. таблица2: имя второй таблицы. условие: условие для выбора строк.
Таблица 2: LateralStudent
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 7 | SOUVIK | DUMDUM | 9876543210 | 18 |
| 8 | NIRAJ | NOIDA | 9786543210 | 19 |
| 9 | SOMESH | ROHTAK | 9687543210 | 20 |
Запросы:
Способ 1 (вставка всех строк и столбцов):
INSERT INTO Student SELECT * FROM LateralStudent;
Результат:
Этот запрос вставит все данные таблицы LateralStudent в таблицу Student. После применения INSERT INTO SQL SELECT таблица Student будет выглядеть следующим образом:
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 1 | Ram | Delhi | 9455123451 | 18 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 4 | SURESH | Delhi | 9156768971 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 7 | SOUVIK | DUMDUM | 9876543210 | 18 |
| 8 | NIRAJ | NOIDA | 9786543210 | 19 |
| 9 | SOMESH | ROHTAK | 9687543210 | 20 |
Способ 2 (вставка отдельных столбцов):
INSERT INTO Student(ROLL_NO,NAME,Age) SELECT ROLL_NO, NAME, Age FROM LateralStudent;
Результат:
Этот запрос вставит данные из столбцов ROLL_NO, NAME и Age таблицы LateralStudent в таблицу Student. Для остальных столбцов таблицы Student будет задано значение null. После применения SQL INSERT SELECT таблица будет выглядеть следующим образом:
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 1 | Ram | Delhi | 9455123451 | 18 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 4 | SURESH | Delhi | 9156768971 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 7 | SOUVIK | Null | null | 18 |
| 8 | NIRAJ | Null | null | 19 |
| 9 | SOMESH | Null | null | 20 |
- Выбор определенных строк для вставки:
INSERT INTO Student SELECT * FROM LateralStudent WHERE Age = 18;
Результат:
Этот запрос выберет только первую строку из таблицы LateralStudent для вставки в таблицу Student. После применения INSERT SELECT таблица будет выглядеть следующим образом:
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
| 1 | Ram | Delhi | 9455123451 | 18 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 4 | SURESH | Delhi | 9156768971 | 18 |
| 3 | SUJIT | ROHTAK | 9156253131 | 20 |
| 2 | RAMESH | GURGAON | 9562431543 | 18 |
| 7 | SOUVIK | DUMDUM | 9876543210 | 18 |
Запрос:
public function query($sql, $params = []) {
$stmt = $this->db->prepare($sql);
if (!empty($params)) {
foreach ($params as $key => $val) {
if (is_int($val)) {
$type = PDO::PARAM_INT;
} else {
$type = PDO::PARAM_STR;
}
$stmt->bindValue(':'.$key, $val, $type);
}
}
$stmt->execute();
return $stmt;
}Вызов:
$params = [
'id' => '',
'name' => $post['name'],
'description' => $post['description'],
];
$this->db->query('INSERT INTO events VALUES (:id, :name, :description)', $params); // print_r
// PDOStatement Object
// (
// [queryString] => INSERT INTO events VALUES (:id, :name, :description)
// )
return $this->db->lastInsertId();Этот код работает:
public function row($sql, $params = []) {
$result = $this->query($sql, $params); // query тот же
return $result->fetchAll(PDO::FETCH_ASSOC);
}
return $this->db->row('SELECT * FROM events ORDER BY id DESC LIMIT :start, :max', $params);Версия php: PHP/7.1.11
Альтернативный и сложный способ выполнения одной и той же задачи приведен ниже. Надеюсь, никому это не нравится. Просто я показываю, что у нас есть еще один альтернативный способ.
declare @us table(name nvarchar(100),lastname nvarchar(100),UID bigint,available bit);
declare @Name nvarchar(100), @lastname nvarchar(100), @UID bigint,@Avail bit
DECLARE ALLRECORDS CURSOR FOR
SELECT name,lastname,UID,available from Users where available='1'
OPEN ALLRECORDS
FETCH NEXT FROM ALLRECORDS INTO @Name,@lastname,@UID,@Avail
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @us
SELECT @Name,@lastname,@UID,@Avail
FETCH NEXT FROM ALLRECORDS INTO @Name,@lastname,@UID,@Avail
END
CLOSE ALLRECORDS
DEALLOCATE ALLRECORDS
SELECT * FROM @us
Всем привет! В данной статье речь пойдет о том, как можно добавлять данные в таблицу в Microsoft SQL Server, если Вы уже хоть немного знакомы с языком T-SQL, то наверно поняли, что сейчас мы будем разговаривать об инструкции INSERT, а также о том, как ее можно использовать для добавления данных в таблицу.
Начнем по традиции с небольшой теории.
Содержание
- Инструкция INSERT в T-SQL
- Исходные данные
- Пример 1 – Добавляем новую запись в таблицу с использованием конструктора табличных значений
- Пример 2 – Добавляем новые строки в таблицу с использованием запроса SELECT
- Пример 3 – Добавляем новые записи в таблицу с использованием хранимой процедуры
INSERT – это инструкция языка T-SQL, которая предназначена для добавления данных в таблицу, т.е. создания новых записей. Данную инструкцию можно использовать как для добавления одной строки в таблицу, так и для массовой вставки данных. Для выполнения инструкции INSERT требуется разрешение на вставку данных (INSERT) в целевую таблицу.
Существует несколько способов использования инструкции INSERT в части данных, которые необходимо вставить:
- Перечисление конкретных значений для вставки;
- Указание набора данных в виде запроса SELECT;
- Указание набора данных в виде вызова процедуры, которая возвращает табличные данные.
Заметка! Начинающим рекомендую посмотреть мой видеокурс по T-SQL.
Упрощённый синтаксис
INSERT [INTO] [таблица] (список столбцов, …)
VALUES (список значений, …)
Или
SELECT запрос на выборку
Или
EXECUTE процедура
Где,
- INSERT INTO – это команда добавления данных в таблицу;
- Таблица – это имя целевой таблицы, в которую необходимо вставить новые записи;
- Список столбцов – это перечень имен столбцов таблицы, в которую будут вставлены данные, разделенные запятыми;
- VALUES – это конструктор табличных значений, с помощью которого мы указываем значения, которые будем вставлять в таблицу;
- Список значений – это значения, которые будут вставлены, разделенные запятыми. Они перечисляются в том порядке, в котором указаны столбцы в списке столбцов;
- SELECT – это запрос на выборку данных для вставки в таблицу. Результирующий набор данных, который вернет запрос, должен соответствовать списку столбцов;
- EXECUTE – это вызов процедуры на получение данных для вставки в таблицу. Результирующий набор данных, который вернет хранимая процедура, должен соответствовать списку столбцов.
Вот примерно так и выглядит упрощённый синтаксис инструкции INSERT INTO, в большинстве случаев именно так Вы и будете добавлять новые записи в таблицы.
Список столбцов, в которые Вы будете вставлять данные, можно и не писать, в таком случае их порядок будет определен на основе фактического порядка столбцов в таблице. При этом необходимо помнить этот порядок, когда Вы будете указывать значения для вставки или писать запрос на выборку. Лично я Вам рекомендую все-таки указывать список столбцов, в которые Вы планируете добавлять данные.
Также следует помнить и то, что в списке столбцов и в списке значений, соответственно, должны присутствовать так называемые обязательные столбцы, это те, которые не могут содержать значение NULL. Если их не указать, и при этом у столбца отсутствует значение по умолчанию, будет ошибка.
Еще хотелось бы отметить, что тип данных значений, которые Вы будете вставлять, должен соответствовать типу данных столбца, в который будет вставлено это значение, ну или, хотя бы, поддерживал неявное преобразование. Но я Вам советую контролировать тип данных (формат) значений, как в списке значений, так и в запросе SELECT.
Хватит теории, переходим к практике.
Исходные данные
Для того чтобы добавлять данные в таблицу, нам нужна соответственно сама таблица, давайте ее создадим, и уже в нее будем пробовать добавлять записи.
Примечание! Все примеры будут выполнены в Microsoft SQL Server 2016 Express.
CREATE TABLE TestTable(
[Id] [INT] IDENTITY(1,1) NOT NULL,
[ProductName] [VARCHAR](100) NOT NULL,
[Price] [Money] NOT NULL
)
Наша тестовая таблица, будет содержать перечень товаров с ценой.
Также в примерах мы будем использовать процедуру, которая возвращает табличное значение, для добавления данных в таблицу, поэтому давайте ее тоже создадим.
CREATE PROCEDURE TestProcedure
AS
BEGIN
SELECT ProductName, Price
FROM TestTable
END
Для примера она у нас будет возвращать данные из только что созданной таблицы TestTable.
Примечание!
Как Вы понимаете, чтение данного материала подразумевает наличные определенных знаний по языку T-SQL, поэтому если Вам что-то непонятно, рекомендую ознакомиться со следующими материалами:
- Справочник по Transact-SQL;
- Основы программирования на T-SQL;
- SQL код – самоучитель по SQL для начинающих программистов;
- Видеокурсы по T-SQL.
Пример 1 – Добавляем новую запись в таблицу с использованием конструктора табличных значений
Сначала давайте попробуем добавить одну запись и сразу посмотрим на результат, т.е. напишем запрос на выборку.
INSERT INTO TestTable(ProductName, Price)
VALUES ('Компьютер', 100)
GO
SELECT * FROM TestTable
Вы видите, что мы после названия таблицы перечислили через запятую имена столбцов, в которые мы будем добавлять данные, затем мы указали ключевое слово VALUES и в скобочках также, в том же порядке, через запятую написали значения, которые мы хотим вставить.
После инструкции INSERT я написал инструкцию SELECT и разделил их командой GO.
А теперь давайте представим, что нам нужно добавить несколько строк. Мы для этого напишем следующий запрос.
INSERT INTO TestTable(ProductName, Price)
VALUES ('Компьютер', 100),
('Клавиатура', 20),
('Монитор', 50)
GO
SELECT * FROM TestTable
В данном случае мы добавили три записи, т.е. три строки. После VALUES значения каждой новой строки указаны в скобочках, разделили мы их запятыми.
Пример 2 – Добавляем новые строки в таблицу с использованием запроса SELECT
Очень часто возникает необходимость добавлять много данных в таблицу, например, на основе запроса на выборку, т.е. SELECT. Для этого вместо VALUES нам всего лишь нужно указать запрос.
INSERT INTO TestTable(ProductName, Price)
SELECT ProductName, Price
FROM TestTable
WHERE Id > 2
GO
SELECT * FROM TestTable
В данном примере мы написали запрос SELECT, который возвращает данные из таблицы TestTable, но не все, а только те, у которых идентификатор больше 2. А результат вставили все в ту же таблицу TestTable.
В качестве примера того, как можно добавлять записи в таблицу без указания списка столбцов, давайте напишем еще один запрос на вставку данных, который сделает равно то же самое что и запрос выше, только в нем не будет перечисления столбцов для вставки.
INSERT INTO TestTable
SELECT ProductName, Price
FROM TestTable
WHERE Id > 2
GO
SELECT * FROM TestTable
В данном случае мы уверены в том, что в таблице TestTable первый столбец это ProductName, а второй Price, поэтому мы можем позволить себе написать именно так. Но, снова повторюсь, на практике лучше указывать список столбцов.
Если Вы заметили, я во всех примерах не указывал столбец Id, а он у нас есть, ошибки не возникло, так как данный столбец со свойством IDENTITY, он автоматически генерирует идентификаторы, поэтому в такой столбец вставить данные просто не получится.
Пример 3 – Добавляем новые записи в таблицу с использованием хранимой процедуры
Сейчас давайте вставим в таблицу данные, которые нам вернёт хранимая процедура. Смысл здесь такой же, вместо VALUES и вместо запроса мы указываем вызов процедуры. Но как Вы понимаете, порядок и количество столбцов, возвращаемых процедурой, должен строго совпадать со списком столбцов для вставки (даже если список столбцов не указан).
INSERT INTO TestTable(ProductName, Price)
EXEC TestProcedure
GO
SELECT * FROM TestTable
Надеюсь, данный материал помог Вам разобраться с инструкцией INSERT INTO, а у меня все, пока!