Содержание

Цикл WHILE для выбора данных за период в T-SQL — Разработка на vc.ru

Зачастую в нашей работе возникает потребность получить набор данных за определенный период. Сделать это можно несколькими способами. В этой статье рассмотрим применение цикла WHILE для задачи поиска расходных операций за несколько месяцев по перечню счетов и сравним его с запросом, в котором весь период будет указан в блоке WHERE.

6639 просмотров

Зачастую в нашей работе возникает потребность получить набор данных за определенный период. Сделать это можно несколькими способами. В этой статье рассмотрим применение цикла WHILE для задачи поиска расходных операций за несколько месяцев по перечню счетов и сравним его с запросом, в котором весь период будет указан в блоке WHERE.

Для начала разберем синтаксис конструкции WHILE. Выглядит он следующим образом:

WHILE [логическое условие] BEGIN [инструкция] END

В блоке Условие находится выражение, возвращающее значение TRUE или FALSE, в блоке Инструкций будет находиться наш запрос на выбор необходимого набора данных.

Блок инструкций необходимо «ограничить» словами управления BEGIN и END.

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

CREATE TABLE [TB44_SANDBOX].[mis].[depohist_test_accounts] ( [account_nbr] nvarchar(255) ) ON [PRIMARY] WITH (DATA_COMPRESSION = PAGE) GO CREATE TABLE [TB44_SANDBOX].[mis].[depohist_test_query] ( [DEPOHIST_ID_MAJOR] bigint ,[DEPOHIST_ID_MINOR] bigint ,[DEPOHIST_ID_MEGA] bigint ,[DEPOSIT_ID_MAJOR] bigint ,[DEPOSIT_ID_MINOR] bigint ,[DEPOSIT_ID_MEGA] bigint ,[PERSON_ID_MAJOR] bigint ,[PERSON_ID_MINOR] bigint ,[PERSON_ID_MEGA] bigint ) ON [PRIMARY] WITH (DATA_COMPRESSION = PAGE) GO CREATE TABLE [TB44_SANDBOX].[mis].[depohist_test_while] ( [DEPOHIST_ID_MAJOR] bigint ,[DEPOHIST_ID_MINOR] bigint ,[DEPOHIST_ID_MEGA] bigint ,[DEPOSIT_ID_MAJOR] bigint ,[DEPOSIT_ID_MINOR] bigint ,[DEPOSIT_ID_MEGA] bigint ,[PERSON_ID_MAJOR] bigint ,[PERSON_ID_MINOR] bigint ,[PERSON_ID_MEGA] bigint ) ON [PRIMARY] WITH (DATA_COMPRESSION = PAGE) GO

Информация об операциях в нашем случае хранится в [backoffice_STG]. [deposit].[VW_depohist], где созданы индексы на номер счета ([deposit_printableno]) и дату операции ([depohist_OpDay]). Далее напишем запрос на выборку и вставку данных с указанием всего периода в блоке WHERE.

INSERT INTO [TB44_SANDBOX].[mis].[depohist_test_query] SELECT [dh].[DEPOHIST_ID_MAJOR] ,[dh].[DEPOHIST_ID_MINOR] ,[dh].[DEPOHIST_ID_MEGA] ,[dh].[DEPOSIT_ID_MAJOR] ,[dh].[DEPOSIT_ID_MINOR] ,[dh].[DEPOSIT_ID_MEGA] ,[dh].[PERSON_ID_MAJOR] ,[dh].[PERSON_ID_MINOR] ,[dh].[PERSON_ID_MEGA] FROM [BACKOFFICE_STG].[DEPOSIT].[VW_DEPOHIST] AS [dh] WITH(NOLOCK) INNER JOIN [TB44_SANDBOX].[mis].[depohist_test_accounts] AS [t] WITH(NOLOCK) ON [dh].[DEPOSIT_PRINTABLENO] = [t].[account_nbr] WHERE [dh].[DEPOHIST_OpCash] < 0 AND [dh].[DEPOHIST_OpDay] >= ‘2020-01-01’ AND [dh].[DEPOHIST_OpDay] < ‘2020-03-01’

Данный запрос выполнялся около полутора минут и вставил в таблицу 1606 строк.

Теперь напишем запрос с использованием цикла WHILE.

DECLARE @startdate date = ‘2020-01-01’ DECLARE @enddate date = ‘2020-03-01’ WHILE @startdate < @enddate BEGIN INSERT INTO [TB44_SANDBOX]. [mis].[depohist_test_while] SELECT [dh].[DEPOHIST_ID_MAJOR] ,[dh].[DEPOHIST_ID_MINOR] ,[dh].[DEPOHIST_ID_MEGA] ,[dh].[DEPOSIT_ID_MAJOR] ,[dh].[DEPOSIT_ID_MINOR] ,[dh].[DEPOSIT_ID_MEGA] ,[dh].[PERSON_ID_MAJOR] ,[dh].[PERSON_ID_MINOR] ,[dh].[PERSON_ID_MEGA] FROM [BACKOFFICE_STG].[DEPOSIT].[VW_DEPOHIST] AS [dh] WITH(NOLOCK) INNER JOIN [TB44_SANDBOX].[mis].[depohist_test_accounts] AS [t] WITH(NOLOCK) ON [dh].[DEPOSIT_PRINTABLENO] = [t].[account_nbr] WHERE [dh].[DEPOHIST_OpCash] < 0 AND [dh].[DEPOHIST_OpDay] = @startdate SET @startdate = DATEADD(DAY, 1, @startdate) END

В начале определяем две переменные, в которых будет находится необходимый период. Далее логическое условие выполнения цикла – дата начала периода строго меньше даты окончания периода. В теле цикла между словами управления BEGIN и END наш запрос на выборку и вставку данных. Обратите внимание, что вместо периода для столбца [dh].[depohist_OpDay] мы указываем конкретное значение, которое содержит переменная @startdate.

После запроса мы присваиваем нашей переменной новое значение в +1 день от текущего значения с помощью функции dateadd(), чтобы перейти на следующий шаг выполнения цикла.

Таким образом наш цикл будет выполняться пока значение переменной @startdate не станет равным значению @enddate, и на каждом шаге цикла будет выполняться запрос на выборку данных за конкретную дату.

Теперь запустим этот запрос и сравним его результаты с предыдущим.

На вкладке «Сообщения» будет несколько строк, которые соответствуют выполненному запросу на каждом шаге цикла. Запрос с использованием цикла выполнялся 35 секунд и вставил в таблицу те же 1606 строк.

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

Оператор 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

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

snumsnamecitycomm
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, который вставляет данные в таблицы, на примерах сами научились это делать. На этом все, если у вас остались вопросы, то оставляйте их в комментариях.

Ускоренная загрузка баз данных SQL с помощью INSERT VALUES

Как правило, базы данных SQL работают лучше всего, когда работают над всей проблемой сразу. один запрос для получения всех необходимых данных — и ничего больше — сразу. То же самое верно и для модификации данных: для удаления трех строк мы должны использовать оператор one delete с правильным предложением where . А как же вставить сразу три строки?

На самом деле существует очень старая функция SQL, делающая это возможным: предложение values ​​ может отображать несколько строк:

 INSERT INTO tbl ( c1 , c2 , c3 )
         ЗНАЧЕНИЯ ('r1c1', 'r1c2', 'r1c3')
              , ('r2c1', 'r2c2', 'r2c3')
              , ('r3c1', 'r3c2', 'r3c3')

Основным недостатком этого подхода является то, что количество строк влияет на саму строку запроса, а также на количество и положение параметров связывания.1 Это не имеет большого значения. для баз данных — в настоящее время почти весь SQL в любом случае выполняется как «динамический SQL». Тем не менее, это может иметь большое значение в вашей кодовой базе. Поэтому в общем случае лучше использовать пакетный API.2

Помимо простоты использования, эти два подхода имеют и семантическое различие: большинство пакетных API просто выполняют оператор несколько раз с оптимизацией задержки. 3 Это означает, что каждое выполнение может завершиться неудачей независимо друг от друга. Если одна из трех строк нарушает ограничение, две другие все равно будут вставлены.

Вставка нескольких строк с помощью одного оператора отличается: стандарт SQL подразумевает семантику «все или ничего» для одиночных операторов.4 Если одна из трех строк в приведенном выше примере нарушает ограничение, ни одна из них не будет вставлена. В зависимости от ваших требований, это может быть или не быть желаемым поведением.

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

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

BigQueryaDb2 (LUW)aMariaDBabMySQLbOracle DBcPostgreSQLaSQL ServeraSQLiteaМногострочная вставка … значения «все или ничего» семантика
  1. Только без ключевого слова row
  2. зависит от механизма хранения: InnoDB: да, MyISAM: нет
    3. 90 032 Не поддерживает вставку … значений с несколькими строками. Insert…select работает и является атомарным

Соответствующие альтернативы

Как упоминалось выше, использование пакетного API — это распространенный способ сократить задержки. Одна очень приятная особенность большинства пакетных API заключается в том, что они также работают для обновить и удалить .

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

Собственные альтернативы

вставить все (Oracle)

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

Об авторе

Маркус Винанд рассказывает о SQL и показывает, как его поддерживают различные системы, на сайте modern-sql.com. Ранее он использовал use-the-index-luke.com, который до сих пор активно поддерживается. Маркуса можно нанять в качестве тренера, спикера и консультанта через winand.at.

Сноски

  1. Исключения подтверждают правило. Возможно, некоторые базы данных чаще ошибаются, чем правы.

  2. В примере для демонстрации используются литеральные значения. Программы всегда должны использовать параметры связывания. Все в порядке, Бобби?

  3. Например, PreparedStatement.addBatch() в Java. Однако прирост производительности зависит от реализации.

  4. Это типичный способ реализации пакетных API. Некоторые API могут предлагать пакетный режим, но за кулисами создавать один оператор. Проверьте свои документы.

  5. «Проверка ограничений»: §4.10.1 в SQL-92 и §4.18.2 в SQL:2011.

Вставка нескольких строк в таблицу SQL Server

Информационный бюллетень сообщества и предстоящие события за май 2023 г. наших участников в сообществах Biz Apps. Если вы новичок в этой группе LinkedIn, обязательно подпишитесь здесь, в новостях и объявлениях, чтобы быть в курсе последних новостей от нашей постоянно растущей сети участников, которые «изменили свое представление о коде». ПОСЛЕДНИЕ НОВОСТИ «Понедельники в Microsoft» в прямом эфире на LinkedIn — 8:00 по тихоокеанскому стандартному времени — понедельник, 15 мая — выпейте утренний кофе в понедельник и присоединяйтесь к главным менеджерам программы Хизер Кук и Каруане Гатиму на премьере эпизода «Понедельники в Microsoft»! Это шоу положит начало запуску нового канала сообщества Microsoft в LinkedIn и охватит целый ряд горячих тем из #PowerPlatform, #ModernWork, #Dynamics365, #AI и всего, что между ними. Просто нажмите на изображение ниже, чтобы зарегистрироваться и присоединиться к команде в прямом эфире в понедельник, 15 мая 2023 года, в 8:00 по тихоокеанскому стандартному времени.
Надеюсь увидеть тебя там! Исполнительный основной доклад | День успеха клиентов Microsoft CVP for Business Applications & Platform, Charles Lamanna, делится последними усовершенствованиями и обновлениями продукта #BusinessApplications, чтобы помочь клиентам достичь результатов в бизнесе. S01E13 Power Platform Connections — 12:00 по тихоокеанскому стандартному времени — четверг, 11 мая В тринадцатом эпизоде ​​Power Platform Connections Хьюго Бернье глубоко погружается в мысли соведущего Дэвида Уорнера II, а также рецензирует замечательные работы Денниса Гедегебюра, Кейта Атертона, Майкла Мегеля, Кэт Шнайдер и других. Нажмите ниже, чтобы подписаться и получить уведомление, с Дэвидом и Хьюго LIVE в чате YouTube с 12:00 по тихоокеанскому стандартному времени. И используйте хэштег #PowerPlatformConnects в социальных сетях, чтобы получить шанс представить свою работу в шоу. ПРЕДСТОЯЩИЕ СОБЫТИЯ Конференция European Power Platform: продажа ранних билетов заканчивается! Ранняя продажа билетов на конференцию European Power Platform заканчивается в пятницу, 12 мая 2023 года! #EPPC23 собирает сообщества Microsoft Power Platform Communities на три дня непревзойденного личного обучения, общения и вдохновения, включая три вдохновляющих основных доклада, шесть полнодневных учебных пособий для экспертов и более восьмидесяти пяти специализированных сессий с приглашенными докладчиками, включая Эйприл Даннэм. , Дона Саркар, Илья Файнберг, Джанет Робб, Даниэль Ласкевиц, Руи Сантос, Йенс Кристиан Шредер, Марко Рокка и многие другие. Подробно ознакомьтесь с последними достижениями продуктов, услышав мнение самых ярких умов в пространстве #PowerApps . Нажмите здесь, чтобы забронировать билет сегодня и сэкономить! Конференция DynamicMinds — Словения — 22-24 мая 2023 г. Уже совсем скоро состоится конференция DynamicsMinds Conference, которая пройдет в Словении с 22 по 24 мая 2023 года, где блестящие умы встречаются, общаются и делятся друг с другом! На этой замечательной конференции Power Platform и Dynamics 365 выступило множество замечательных спикеров, в том числе Георг Гланчниг, Дона Саркар, Томми Скауэ, Моник Хейворд, Александр Тотович, Рэйчел Профитт, Орельен Клер, Ана Инес Уррутиа де Соуза, Лука Пеллегрини, Бостьян Голоб, Шеннон Маллинз, Елена Баева, Иван Фикко, Гуро Фаллер, Вивиан Восс, Эндрю Бибби, Триша Синклер, Роджер Гилкрист, Сара Лагерквист, Стив Мордью и многие другие. Нажмите здесь: Конференция DynamicsMinds для получения дополнительной информации об этой потрясающей конференции сообщества, охватывающей все аспекты Power Platform и не только. Конференция «Дни знаний» в Дании — 1-2 июня 2023 г. Посетите «Дни знаний» — конференцию партнеров Directions 4, которая пройдет 1–2 июня в Оденсе, Дания и посвящена обучению сотрудников, обмену знаниями и повышению квалификации специалистов Business Central. Эта фантастическая двухдневная конференция предлагает сочетание учебных занятий и семинаров, главной темой которых является Business Central и сопутствующие продукты. Существует большой список отраслевых экспертов, которые делятся своими знаниями, в том числе Иона В., Берт Вербек, Лиза Юхлин, Дуглас Роман, Каролина Эдвинссон, Ким Далсгаард Кристенсен, Инга Сартаускайте, Пейк Бех-Андерсен, Шеннон Маллинз, Джеймс Кроутер, Мона Боркстед Нильсен, Ренато Файдига, Вивиан Восс, Свен Ноомен, Паулиен Бускенс, Андри Мар Хельгасон, Кейлин Ханниган, Фредди Кристиансен, Сигне Агербо, Люк ван Вугт и многие другие. Если вы хотите встретиться с отраслевыми экспертами, получить преимущество на рынке малого и среднего бизнеса и получить новые знания о Microsoft Dynamics Business Central, нажмите здесь Конференция Days of Knowledge в Дании, чтобы купить билет сегодня! ОСОБЕННОСТИ СООБЩЕСТВА Посмотрите, как наши лучшие пользователи Super и Community достигают новых уровней! Эти трудолюбивые участники публикуют сообщения, отвечают на вопросы, хвалят и предлагают лучшие решения в своих сообществах.

No related posts.