Ms sql select into пример: Предложение INTO (Transact-SQL) — SQL Server

Инструкция SELECT INTO в T-SQL или как создать таблицу на основе SQL запроса? | Info-Comp.ru

Если у Вас возникала необходимость сохранить результирующий набор данных, который вернул SQL запрос, то данная статья будет Вам интересна, так как в ней мы рассмотрим инструкцию SELECT INTO, с помощью которой в Microsoft SQL Server можно создать новую таблицу и заполнить ее результатом SQL запроса.

Начнем мы, конечно же, с описания самой инструкции SELECT INTO, а затем перейдем к примерам.

Содержание

1. Инструкция SELECT INTO в Transact-SQL
2. Важные моменты про инструкцию SELECT INTO
3. Примеры использования SELECT INTO
4. Исходные данные
5. Пример 1 – Создание таблицы с помощью инструкции SELECT INTO с объединением данных
6. Пример 2 – Создание временной таблицы с помощью инструкции SELECT INTO с группировкой данных

Инструкция SELECT INTO в Transact-SQL

SELECT INTO – инструкция в языке в T-SQL, которая создает новую таблицу и вставляет в нее результирующие строки из SQL запроса. Структура таблицы, т.е. количество и имена столбцов, а также типы данных и свойства допустимости значений NULL, будут на основе столбцов (выражений), указанных в списке выбора из источника в инструкции SELECT. Обычно инструкция SELECT INTO используется для объединения в одной таблице данных из нескольких таблиц, представлений, включая какие-то расчетные данные.

Для того чтобы использовать инструкцию SELECT INTO требуется разрешение CREATE TABLE в базе данных, в которой будет создана новая таблица.

Инструкция SELECT INTO имеет два аргумента:

• new_table — имя новой таблицы;
• filegroup – файловая группа. Если аргумент не указан, то используется файловая группа по умолчанию. Данная возможность доступна начиная с Microsoft SQL Server 2017.

Заметка! Начинающим рекомендую посмотреть мой видеокурс по T-SQL.

Важные моменты про инструкцию SELECT INTO

• Инструкцию можно использовать для создания таблицы на текущем сервере, на удаленном сервере создание таблицы не поддерживается;
• Заполнить данными новую таблицу можно как с текущей базы данных и текущего сервера, так и с другой базы данных или с удаленного сервера. Например, указывать полное имя базы данных в виде база_данных.схема.имя_таблицы или в случае с удаленным сервером, связанный_сервер.база_данных.схема.имя_таблицы;
• Столбец идентификаторов в новой таблице не наследует свойство IDENTITY, если: инструкция содержит объединение (JOIN, UNION), предложение GROUP BY, агрегатную функцию, также, если столбец идентификаторов является частью выражения, получен из удаленного источника данных или встречается более чем один раз в списке выбора. Во всех подобных случаях столбец идентификаторов не наследует свойство IDENTITY и создается как NOT NULL;
• С помощью инструкции SELECT INTO нельзя создать секционированную таблицу, даже если исходная таблица является секционированной;
• В качестве новой таблицы можно указать обычную таблицу, а также временную таблицу, однако нельзя указать табличную переменную или возвращающий табличное значение параметр;
• Вычисляемый столбец, если такой есть в списке выбора инструкции SELECT INTO, в новой таблице он становится обычным, т. е. не вычисляемым;
• SELECT INTO нельзя использовать вместе с предложением COMPUTE;
• С помощью SELECT INTO в новую таблицу не переносятся индексы, ограничения и триггеры, их нужно создавать дополнительно, после выполнения инструкции, если они нужны;
• Предложение ORDER BY не гарантирует, что строки в новой таблице будут вставлены в указанном порядке.
• В новую таблицу не переносится атрибут FILESTREAM. Объекты BLOB FILESTREAM в новой таблице будут как объекты BLOB типа varbinary(max) и имеют ограничение в 2 ГБ;
• Объем данных, записываемый в журнал транзакций во время выполнения операций SELECT INTO, зависит от модели восстановления. В базах данных, в которых используется модель восстановления с неполным протоколированием, и простая модель, массовые операции, к которым относится SELECT INTO, минимально протоколируются. За счет этого инструкция SELECT INTO может оказаться более эффективней, чем отдельные инструкции по созданию таблицы и инструкции INSERT по заполнение ее данными.

Примеры использования SELECT INTO

Все примеры я буду выполнять в СУБД Microsoft SQL Server 2016 Express.

Исходные данные

Для начала давайте создадим две таблицы и заполним их данными, эти таблицы мы и будем объединять в примерах.

   
   CREATE TABLE TestTable(
        [ProductId] [INT] IDENTITY(1,1) NOT NULL,
        [CategoryId] [INT] NOT NULL,
        [ProductName] [VARCHAR](100) NOT NULL,
        [Price] [money] NULL
   ) ON [PRIMARY]

   GO

   CREATE TABLE TestTable2(
        [CategoryId] [INT] IDENTITY(1,1) NOT NULL,
        [CategoryName] [VARCHAR](100) NOT NULL
   ) ON [PRIMARY]

   GO

   INSERT INTO TestTable
        VALUES (1,'Клавиатура', 100),
                   (1, 'Мышь', 50),
                   (2, 'Телефон', 300)
   GO

   INSERT INTO TestTable2
        VALUES ('Комплектующие компьютера'),
                   ('Мобильные устройства')
   GO
                  
   SELECT * FROM TestTable

   SELECT * FROM TestTable2

Пример 1 – Создание таблицы с помощью инструкции SELECT INTO с объединением данных

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

   
   --Операция SELECT INTO
   SELECT T1.ProductId, T2.CategoryName, T1.ProductName, T1.Price
   INTO TestTable3 
   FROM TestTable T1
   LEFT JOIN TestTable2 T2 ON T1.CategoryId = T2.CategoryId

   --Выборка данных из новой таблицы
   SELECT * FROM TestTable3

В итоге мы создали таблицу с названием TestTable3 и заполнили ее объединёнными данными.

Пример 2 – Создание временной таблицы с помощью инструкции SELECT INTO с группировкой данных

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

   
   --Создаем временную таблицу (#TestTable) с помощью инструкции SELECT INTO
   SELECT T2.CategoryName, COUNT(T1. ProductId) AS CntProduct
   INTO #TestTable
   FROM TestTable T1
   LEFT JOIN TestTable2 T2 ON T1.CategoryId = T2.CategoryId
   GROUP BY T2.CategoryName

   --Выборка данных из временной таблицы
   SELECT * FROM #TestTable

Как видим, у нас получилось создать временную таблицу #TestTable и заполнить ее сгруппированными данными.

Вот мы с Вами и рассмотрели инструкцию SELECT INTO в языке T-SQL, всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL, которую написал я, и в которой я подробно, и в то же время простым языком, рассказываю о языке SQL, а у меня на этом все, пока!

Заметка! Для профессионального изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL.

Команда SQL GRANT для назначения привилегий пользователям

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

Команда GRANT используется для назначения привилегий пользователям.

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

Синтаксис команды GRANT

• system_priv — системная привилегия
• role — роль: набор соответствующих полномочий, которые администратор может коллективно предоставлять пользователям и другим ролям.
• user — пользователь
• PUBLIC —  привилегия передается всем пользователям
• WITH ADMIN OPTION — если предоставлены системные полномочия или роли, то параметр позволяет пользователю передать полномочие или роль другим пользователям или ролям

SQL GRANT. Пример 1

Предположим, пользователь Р1 является владельцем таблицы Student и нужно передать пользователю Р2 право на формулирование запросов к этой таблице:

GRANT SELECT ON Student TO P2;

SQL GRANT. Пример 2

Для передачи прав на другие привилегии синтаксис тот же самый. Пользователь Р1, являющийся владельцем таблицы Student, может разрешить пользователю Р2 вводить строки в нее:

GRANT INSERT ON Student TO P2;

SQL GRANT. Пример 3

Передача привилегий не ограничивается передачей единственной привилегии единственному пользователю с помощью одной команды GRANT. Допустимы списки привилегий и/или пользователей с элементами, разделенными запятыми:

GRANT SELECT, INSERT ON Student TO P2; GRANT SELECT, INSERT ON Student TO P2, Р3;

SQL GRANT. Пример 4

Можно разрешить пользователю изменять значения любого или всех столбцов таблицы:

GRANT UPDATE ON Student TO P2; GRANT UPDATE (Fam,Ball) ON Student TO P2;

SQL GRANT. Пример 5

Если необходимо предоставить кому-то все полномочия на конкретный объект, используется ключевое слово ALL:

GRANT ALL ON Student ТО Р2;

SQL GRANT. Пример 6

Когда передаются привилегии с атрибутом PUBLIC, который относится  к пользователям, а не к привилегиям, то все пользователи получают их автоматически. Чаще всего это применяется для привилегии SELECT для определенных таблиц или представлений, которые нужно предоставить каждому пользователю для рассмотрения. Разрешить каждому пользователю просматривать таблицу Student можно следующей командой:

GRANT SELECT ON Student TO PUBLIC;

SQL GRANT. Пример 7

Иногда создатель таблицы хочет, чтобы другие пользователи имели право передавать привилегии на эту таблицу. Это можно сделать с помощью предложения WITH GRANT OPTION. Если пользователь Р1 желает, чтобы пользователь Р2 имел право передавать полномочия на работу с таблицей Student другим пользователям, то он должен передать пользователю Р2 привилегию на выполнение соответствующих команд:

GRANT SELECT, INSERT ON Student ТО Р2 WITH GRANT OPTION;

Оператор SQL Server SELECT INTO с объяснением на примерах

Резюме : в этом руководстве вы узнаете, как использовать оператор SQL Server SELECT INTO для копирования таблицы.

Введение в SQL Server

Оператор SELECT INTO

Оператор SELECT INTO создает новую таблицу и вставляет в нее строки из запроса.

Следующий оператор SELECT INTO создает целевую таблицу и копирует строки, которые удовлетворяют ГДЕ условие, из исходной таблицы в таблицу назначения :

 
  SELECT
    select_list
В
    место назначения
ОТ
    источник
[ГДЕ условие]
  
  Язык кода: SQL (язык структурированных запросов) (sql)  

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

Обратите внимание, что оператор SELECT INTO не копирует ограничения, такие как первичный ключ и индексы, из исходной таблицы в таблицу назначения .

SQL Server

SELECT INTO примеров

Давайте рассмотрим несколько примеров использования инструкции SELECT INTO .

A) Использование SQL Server

SELECT INTO для копирования таблицы в той же базе данных пример

Сначала создайте новую схему для хранения новой таблицы.

 
  СОЗДАТЬ СХЕМУ маркетинга;
ИДТИ
  
  Язык кода: SQL (язык структурированных запросов) (sql)  

Во-вторых, создайте таблицу marketing. customers , аналогичную таблице sales.customers , и скопируйте все строки из таблицы sales.customers в таблицу marketing.customers таблица:

 
 ВЫБЕРИТЕ
    *
В
    маркетинг.клиенты
ОТ
    продажи.клиенты;
  
  Язык кода: SQL (язык структурированных запросов) (sql)  

В-третьих, запросите данные из таблицы marketing.customers для проверки копии:

 
  SELECT
    *
ОТ
    маркетинг.клиенты;
  
  Язык кода: SQL (язык структурированных запросов) (sql)  

На следующем рисунке показан частичный вывод:

B) Использование SQL Server

Оператор SELECT INTO для копирования таблицы между базами данных

Сначала создайте новую базу данных с именем TestDb для тестирования:

 
  CREATE DATABASE TestDb;
ИДТИ
  
  Язык кода: SQL (язык структурированных запросов) (sql)  

Во-вторых, скопируйте sales. customers из текущей базы данных ( BikeStores ) в таблицу TestDb.dbo.customers . На этот раз мы просто копируем идентификатор клиента, имя, фамилию и адрес электронной почты клиентов, которые находятся в Калифорния :

 
  ВЫБЕРИТЕ
    Пользовательский ИД,
    имя,
    фамилия,
    электронная почта
В
    TestDb.dbo.customers
ОТ
    продажи.клиенты
ГДЕ
    состояние = 'ЦС';
  
  Язык кода: SQL (язык структурированных запросов) (sql)  

В-третьих, запросите данные из TestDb.dbo.customers для проверки копии:

 
  SELECT
    *
ОТ
    TestDb.dbo.customers;
  
  Язык кода: SQL (язык структурированных запросов) (sql)  

Вот частичный набор результатов:

В этом руководстве вы узнали, как использовать оператор SQL Server SELECT INTO для копирования таблицы в одной базе данных или между базами данных.

Оператор SQL SELECT INTO

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

Мы регулярно вставляем данные в таблицы SQL Server либо из приложения, либо непосредственно в SSMS. Мы можем вставлять данные с помощью оператора INSERT INTO. Для этого у нас уже должна быть таблица для вставки в нее данных, поскольку мы не можем создать таблицу, используя Insert into the statement.

Нам нужно выполнить следующие задачи, используя оператор INSERT INTO.

• Создайте структуру таблицы с соответствующими типами данных
• Вставьте в него данные

Но хорошая новость заключается в том, что мы можем элегантно выполнить обе задачи вместе, используя оператор SQL SELECT INTO. Он создает структуру таблицы для столбцов, возвращаемых оператором Select.

Предположим, мы хотим обновить много записей в таблице. Мы можем использовать оператор SELECT INTO для создания резервной таблицы с существующей структурой исходной таблицы. Давайте рассмотрим SELECT INTO в этой статье.

Синтаксис оператора SELECT INTO

Параметры в операторе SELECT INTO

• Список столбцов: Нам нужно указать столбец, который мы хотим получить и вставить в новую таблицу
• New_table: Здесь мы можем указать имя новой таблицы. SQL Server создает новую таблицу со столбцами, указанными в списке столбцов. Мы не можем заменить существующую таблицу, используя это. Имя таблицы должно быть уникальным
• Таблицы: Содержит таблицу, из которой мы хотим получить записи. Мы также можем иметь несколько таблиц, определенных здесь, с правильным предложением Join
• Условия WHERE: Мы можем фильтровать записи, используя предложение Where. Это необязательный пункт

Давайте рассмотрим оператор SQL SELECT INTO на примерах.

Окружающая среда:

В этом примере мы используем учебную базу данных AdventureWorks2017. Предположим, мы хотим выбрать записи из таблицы [Employee] и создаем новую таблицу [Employee_Demo] с помощью оператора SELECT INTO. Нам нужны выбранные столбцы только в новой таблице. Давайте запустим оператор select для таблицы Employee со столбцами, которые мы хотим иметь.

Прежде чем выполнить оператор SQL SELECT INTO, мы можем убедиться, что таблица Employee_Demo не существует в базе данных AdventureWorks2017 , используя команду sp_help .

На следующем снимке экрана видно, что таблицы Employee_Demo в моей базе данных не существует.

Выполните следующий запрос, чтобы создать новую таблицу [Employee_Demo], используя оператор SELECT INTO.

Мы получаем следующее выходное сообщение в Azure Data Studio. Вы можете заметить, что количество затронутых строк равно 10. В запросе мы выбрали 10 лучших записей из 9.0003 Сотрудник табл.

Мы можем получить доступ к данным из вновь созданной таблицы Employee_Demo и убедиться, что она содержит те же записи, что и в нашем предыдущем операторе выбора.

Мы не указывали никаких свойств столбца в операторе SQL SELECT INTO. Давайте сравним столбцы исходной и целевой таблиц и их свойства.

Мы можем запустить команду sp_help ‘tablename’ на обоих Сотрудник и Таблица Employee_Demo . Я скопировал вывод обеих таблиц в Excel (для выбранных столбцов). На следующем снимке экрана видно, что тип данных столбца и их свойства аналогичны.

В предыдущем операторе SQL SELECT INTO мы подготовили целевую таблицу (Employee_Demo) из нескольких столбцов исходной таблицы (Employee).

Давайте создадим еще одну таблицу со всеми столбцами в таблице сотрудников со следующим запросом.

В выводе мы видим, что он вставил 290 строк в таблицу Employee_All .

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

Предположим, у нас есть первичный и внешний ключи в исходной таблице. Создает ли он первичный ключ и внешний ключ, аналогичные исходной таблице? Нет, оператор SQL SELECT INTO не создает никаких ключей в целевой таблице. Если мы хотим, мы можем определить ключи в таблице назначения. Проверим это в следующем разделе.

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

Мы видим, что таблица Employee содержит первичный и внешний ключи.

Мы скопировали все столбцы в Employee_All из Employee таблицы . Теперь нам нужно проверить первичный и внешний ключ в таблице назначения.

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

SQL SELECT INTO — вставка данных из нескольких таблиц

В предыдущих примерах мы создали таблицу, используя Оператор SELECT INTO из одной таблицы Сотрудник . Мы также можем объединить несколько таблиц и использовать оператор SELECT INTO для создания новой таблицы с данными. В этом разделе мы хотим объединить несколько таблиц вместе. В следующем запросе мы объединили следующие таблицы вместе в AdventureWorks2017.

• [Отдел кадров].[Сотрудник]
• [Человек].[Человек]
• [Персона].[BusinessEntityAddress]
• [Лицо].[Адрес]
• [Человек].[ШтатПровинция]
• [Человек].[СтранаРегион]
• [Человек]. [PersonPhone]
• [Человек].[ТипНомераТелефона]
• [Человек].[Адрес электронной почты]

Выполните следующий запрос. Он дает результаты из нескольких таблиц на основе условия соединения и упомянутых столбцов.

Выполните запрос, и мы получим следующие выходные данные оператора SQL SELECT INTO .

Мы хотим создать таблицу с данными, возвращаемыми с помощью приведенной выше таблицы. Давайте выполним оператор SQL SELECT INTO.

Он создает таблицу [HumanResources].[Employee_JoinTables] и вставляет в нее данные. Мы можем проверить записи в этой таблице с помощью оператора select.

Мы видим, что вы можете объединить несколько таблиц вместе и создать выходную таблицу, используя оператор SELECT INTO.

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

Ошибка 1: из-за несоответствия типов данных

Сообщение 245, уровень 16, состояние 1, строка 1
Ошибка преобразования при преобразовании значения varchar «GG» в тип данных int.

Ошибка 2: сообщение 8152, уровень 16, состояние 30, строка 2

Строка или двоичные данные будут проигнорированы.

Мы не получаем эти ошибки при вставке данных с помощью инструкции SQL SELECT INTO. Однако мы не можем вставлять данные в существующие таблицы, используя этот метод.

SELECT INTO — указать файловую группу

В предыдущих разделах мы рассмотрели возможность создания новой таблицы и вставки в нее данных с помощью инструкции SQL SELECT INTO из существующих таблиц. Он создает таблицу только в файловой группе по умолчанию. Мы не можем указать конкретную файловую группу до SQL Server 2016. В SQL Server 2017 мы можем указать конкретную файловую группу, в которой должна быть создана новая таблица. SQL Server создает новую таблицу в этой конкретной таблице Verify Table Filegroup. Если мы не укажем какие-либо файловые группы в SQL Server 2017 и более поздних версиях, будет создана таблица в файловой группе по умолчанию.

Примечание: Мы не можем указать файловые группы в SQL SELECT INTO для SQL Server 2016 и более ранних версий.

Давайте добавим новую файловую группу в базу данных AdventureWorks2017. Щелкните правой кнопкой мыши базу данных и перейдите к Filegroups .

На этой странице Filegroup нажмите Add FileGroup и укажите имя для Filegroup.

Теперь щелкните Files , и в нем будут перечислены существующие файлы базы данных (данные и файл журнала), на этой странице добавьте новый файл данных и укажите FileGroup из раскрывающегося списка. Это должна быть FileGroup, которую мы только что создали.

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

В следующем запросе вы можете видеть, что мы указали имя файловой группы, используя предложение ON. Он работает аналогично обычному оператору SQL SELECT INTO с разницей в файловой группе.

После создания таблицы выполните команду sp_help для этой вновь созданной таблицы. На следующем снимке экрана мы можем убедиться, что таблица расположена в INSERTFILE FileGroup. Это файловая группа, которую мы создали ранее.

Мы также можем проверить это из свойств таблицы. Щелкните правой кнопкой мыши таблицу в SSMS. В разделе Storage видим нужную FileGroup.

SELECT INTO с условием Where

Предположим, мы хотим создать таблицу с оператором SQL SELECT INTO с несколькими записями в ней. Мы можем использовать предложение Where , аналогичное оператору select. В следующем запросе мы хотим создать таблицу person.person_WC для человека с фамилией вроде %Duf%.

Заключение

В этой статье мы рассмотрели оператор SQL SELECT INTO и сценарии его использования. Это полезная команда для создания копии таблицы без указания типов данных.

• Автор
• Последние сообщения

Раджендра Гупта

Привет! Я Раджендра Гупта, специалист по базам данных и архитектор, помогаю организациям быстро и эффективно внедрять решения Microsoft SQL Server, Azure, Couchbase, AWS, устранять связанные проблемы и настраивать производительность с более чем 14-летним опытом.