Как сформировать на языке T-SQL большое количество строк с тестовыми данными и вставить в таблицу

Всем привет! Сегодня я расскажу о том, как можно на языке T-SQL сгенерировать большое количество строк с какими-то схожими данными и вставить в таблицу, при этом вся эта процедура будет выполняться всего несколько секунд!

Заметка! Транзакции в T-SQL – основы для новичков с примерами.

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

Однако в любом случае возникает вопрос – как сформировать строки с подобными данными на T-SQL и вставить их в таблицу?

Если речь идет о 10, 20, 30 записях, то проблемы, конечно же, никакой нет, Вы можете просто 10 раз выполнить одну инструкцию INSERT, но когда речь заходит о сотнях тысяч строк или миллионах, то простой инструкцией здесь не обойтись, поэтому у тех, кто впервые сталкивается с такой задачей, возникает вопрос: какой же механизм использовать, чтобы сформировать и вставить строки в таблицу.

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

Предлагаю рассмотреть данный механизм на решении конкретной задачи.

Содержание

Условие задачи
Формирование строк с данными на T-SQL и добавление их в таблицу
Решение задачи

Условие задачи

Есть база данных в Microsoft SQL Server, необходимо создать таблицу с 1 миллионом строк.

Данные должны содержать:

Последовательность чисел от 1 до миллиона;
Последовательность дат с точностью до часа и интервалом в час;
Любые текстовые данные.

Заметка! Статистика в Microsoft SQL Server – что это такое и для чего она нужна.

Формирование строк с данными на T-SQL и добавление их в таблицу

Первое, что приходит на ум для решения данной задачи – это использовать цикл, и в нем с помощью какого-нибудь алгоритма сформировать последовательность чисел и дат.

Однако, если мы будем использовать этот механизм, то нас ждёт разочарование.

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

Отсюда можно сделать вывод.

Не нужно использовать цикл для формирования большого количества строк.

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

Для решения нашей задачи нам потребуется обобщенное табличное выражение (CTE), объединение CROSS JOIN, ну и конструктор табличных значений (VALUES) для более компактной реализации.

Суть решения в следующем: мы сформируем десять строк данных с помощью конструктора табличных значений, можно использовать и объединение UNION, но вариант с VALUES более компактный. Затем мы с помощью CROSS JOIN будем соединять эти данные друг с другом, а как Вы помните, результат CROSS JOIN – это декартово произведение. Таким образом, чтобы создать 1 миллион строк нам достаточно 5 раз сделать CROSS JOIN.

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

Для создания последовательности чисел мы будем использовать обычное IDENTITY в таблице, а для последовательности дат используем небольшое сочетание оконной функции ROW_NUMBER и функции DATEADD.

Заметка! XACT_ABORT в T-SQL – что это такое и как использовать.

Решение задачи

   
   --Создание таблицы
   CREATE TABLE TestTable (
	   RowNumber INT IDENTITY(1,1) PRIMARY KEY, 
	   Dt DATETIME, 
	   Сomment VARCHAR(100)
   );
   --Формирование и вставка данных в таблицу
   WITH SrcRows AS ( 
	SELECT NumberRow
	FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS SR (NumberRow)
   ) 
   INSERT TestTable (Dt, Сomment)
	SELECT DATEADD(hh, RowNumber-1, '19070101') Dt, 
		   'Текстовые данные ' + CAST(RowNumber AS VARCHAR(100)) AS Сomment
	FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowNumber 
		  FROM SrcRows AS Ten, 
			   SrcRows AS Hundred,
			   SrcRows AS Thousand, 
			   SrcRows AS TenThousand, 
			   SrcRows AS HundredThousand,
			   SrcRows AS Million
		  )  AS ResultingRows; 
   --Выборка данных
   SELECT * FROM TestTable;

Заметка! Назначение хранимых процедур в языке T-SQL.

Как Вы могли заметить, для соединения CROSS JOIN я использовал классический способ, т.е. явно не указывал инструкцию CROSS JOIN, я просто в секции FROM перечислил таблицы и никаких условий не ставил, а как Вы, наверное, знаете, если не указывать условия при таком синтаксисе, будет происходить как раз соединение CROSS JOIN. Это сделано специально для формирования более компактной инструкции. Однако в обычных ситуациях при соединениях разных таблиц рекомендовано использовать синтаксис с явным указанием инструкции JOIN, т.е. INNER JOIN, LEFT JOIN и так далее.

В результате мы сформировали и вставили в таблицу 1 миллион записей, при этом у нас есть последовательность чисел и последовательность дат, в нашем случае в каждой строке у нас хранится временная отметка с интервалом в час, начиная с 1907 года)). Просто с этого года до нашего времени прошло 1 миллион часов.

Инструкция добавления данных даже на моем обычном компьютере (не сервере), работала всего 1 секунду, плюс еще выборка работала несколько секунд, итого вся инструкция выполняется за 5-6 секунд. Для миллиона строк, я думаю, неплохо, если сравнивать с циклом))

Если у Вас возникла необходимость добавить не миллион строк, а больше, то просто добавьте еще одно соединение CROSS JOIN с этими же данными, т.е. с CTE. Если наоборот необходимо добавить меньше строк данных, то уберите одно или несколько соединений.

В тех случаях, когда требуется сформировать и вставить в таблицу точное количество строк, допустим 850 123, Вы можете использовать обычное условие WHERE, чтобы ограничить выборку для вставки в таблицу.

   
   --Создание таблицы
   CREATE TABLE TestTable (
	RowNumber INT IDENTITY(1,1) PRIMARY KEY, 
	Dt DATETIME, 
	Сomment VARCHAR(100)
   );
   --Формирование и вставка данных в таблицу
   WITH SrcRows AS ( 
	SELECT NumberRow
	FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS SR (NumberRow)
   ) 
   INSERT TestTable (Dt, Сomment)
	SELECT DATEADD(hh, RowNumber-1, '19070101') Dt, 
		   'Текстовые данные ' + CAST(RowNumber AS VARCHAR(100)) AS Сomment
	FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowNumber 
		  FROM SrcRows AS Ten, 
			   SrcRows AS Hundred,
			   SrcRows AS Thousand, 
			   SrcRows AS TenThousand, 
			   SrcRows AS HundredThousand,
			   SrcRows AS Million
		  )  AS ResultingRows
	WHERE RowNumber <= 850123; --Количество строк для вставки 
   --Выборка данных
   SELECT * FROM TestTable;

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

На сегодня это все, надеюсь, статья была Вам полезна, пока!