Создание временной таблицы из CTE запроса в MS SQL

Быстрый ответ

Чтобы в SQL Server создать временную таблицу используя общее табличное выражение CTE (Common Table Expressions), выполните следующий код:

;WITH CTE AS (SELECT * FROM OriginalTable)
SELECT * INTO #TempTable FROM CTE;

Перед WITH обязательно ставится точка с запятой, после которой можно использовать команду SELECT * INTO, чтобы перенести данные во временную таблицу #TempTable.

Подробное руководство и примеры кода

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

Составление CTE — основа для создания временных таблиц

Тщательное составление CTE высоко ценится, особенно при работе с большим объемом данных. Выбор нужных колонок и строк значительно упрощает последующую работу.

;WITH WeeklyData AS (
  SELECT * FROM OriginalTable WHERE DateColumn >= GETDATE() – 7
)
SELECT * INTO #TempTable FROM WeeklyData;

В данном примере мы отфильтровываем данные за последнюю неделю, что позволяет упрощать работу с более узкими данными.

Осуществляем выборку данных — по одному параметру

Советуется аккуратно подойти к выбору колонок в запросе SELECT INTO для достижения оптимальной производительности ваших запросов.

Подчистка после выполнения работы — удаление временной таблицы

Очистка кода от лишних временных таблиц осуществляется при помощи команды DROP TABLE #TempTable. Несмотря на то, что SQL Server сам удаляет временные таблицы после окончания сеанса, ручное удаление является хорошей практикой.

Визуализация

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

Рецепт (🗒️): SELECT * FROM MasterChefBook
Кухня (🍳): Операция с временной таблицей

Теперь визуализируем процесс превращения рецепта в успешное блюдо:

WITH Recipe AS (SELECT * FROM MasterChefBook)
SELECT * INTO Kitchen FROM Recipe;

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

Использование рекурсии в CTE

Рекурсия в CTE становится незаменимой, когда, например, нужно сгенерировать серию дат. Если предполагается более 100 рекурсий, не забудьте использовать OPTION (MAXRECURSION 0).

;WITH DateSeries AS (
    SELECT CAST(GETDATE() AS DATE) AS DateValue
    UNION ALL
    SELECT DATEADD(DAY, 1, DateValue) FROM DateSeries WHERE DateValue < DATEADD(DAY, 10, GETDATE())
)
SELECT * INTO #DateList FROM DateSeries OPTION (MAXRECURSION 0);

Здесь создается временная таблица #DateList, которая включает в себя даты на десять дней вперед от текущей даты.

Важность стандартизации именования

Использование псевдонимов и соблюдение стандартов наименования значительно улучшают читаемость SQL-запросов.

;WITH SalesData AS (
    SELECT OrderID, OrderDate, TotalAmount FROM Orders WHERE OrderDate >= @StartDate
)
SELECT sd.OrderID, sd.OrderDate, sd.TotalAmount INTO #FilteredSales FROM SalesData AS sd;

Эффективное использование ресурсов SQL Server

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

Полезные материалы

1. Рекурсивные запросы с использованием CTE в SQL Server — Подробная информация о рекурсивных запросах с использованием CTE.
2. Временные таблицы в SQL Server — Анализ использования временных таблиц.
3. CTE в SQL Server: примеры — Практические примеры использования CTE.
4. Производительность SQL Server: IN против EXISTS — Дискуссия о производительности, включая контекст использования временных таблиц и CTE.
5. Упрощение SQL-запросов с помощью CTE — Рекомендации по упрощению запросов с использованием CTE.