Предложение SELECT ...INTO (Transact-SQL)

Статья
05/23/2023

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure хранилище платформы Аналитики Azure Synapse Analytics (PDW) в Microsoft Fabric

Инструкция SELECT…INTO создает новую таблицу в файловой группе по умолчанию и вставляет в нее результирующие строки из запроса. Полный синтаксис SELECT см. в разделе SELECT (Transact-SQL).

Соглашения о синтаксисе Transact-SQL

Синтаксис

[ INTO new_table ]
[ ON filegroup ]

Аргументы

new_table
Указывает имя новой таблицы, создаваемой на основе столбцов, указанных в списке выбора, и строк, выбираемых из источника данных.

Формат аргумента new_table определяется путем расчета выражений, указанных в списке выбора. Столбцы в таблице, указанной в аргументе new_table, создаются в порядке, соответствующем списку выбора. Все столбцы таблицы, указанной в аргументе new_table, получают такие же имена, значения, типы данных и свойства допустимости значений NULL, которые указаны в соответствующем выражении в списке выбора. Свойство IDENTITY столбца переносится за исключением случаев, когда наступают условия, описанные в подразделе «Примечания» раздела «Работа со столбцами идентификаторов».

Чтобы создать таблицу в другой базе данных в том же экземпляре SQL Server, укажите new_table в качестве полного имени в форме database.schema.table_name.

new_table нельзя создать на удаленном сервере, однако new_table можно заполнить из удаленного источника данных. Для создания таблицы new_table из удаленного источника таблицы определите источник таблицы, используя четырехчастное имя в форме linked_server.catalog.schema.object в предложении FROM инструкции SELECT. Для указания удаленного источника данных также можно использовать функцию OPENQUERY или функцию OPENDATASOURCE в предложении FROM.

filegroup
Указывает имя файловой группы, в которой будет создана таблица. Указанная файловая группа должна существовать в базе данных, в противном случае обработчик SQL Server создает ошибку.

Область применения: SQL Server 2016 (13.x) с пакетом обновления 2 (SP2) и более поздних версий.

Типы данных

Атрибут FILESTREAM не переносится в новую таблицу. Объекты BLOB FILESTREAM копируются и хранятся в новой таблице как объекты BLOB типа varbinary(max). Без атрибута FILESTREAM тип данных varbinary(max) имеет ограничение в 2 ГБ. Если размер большого двоичного объекта FILESTREAM превышает это значение, происходит ошибка 7119 и инструкция прекращает работу.

При выборе существующего столбца идентификаторов в новой таблице новый столбец наследует свойство IDENTITY, если не выполняется ни одно из следующих условий.

Инструкция SELECT содержит соединение.
несколько инструкций SELECT соединены при помощи UNION;
столбец идентификаторов встречается более чем один раз в списке выбора;
столбец идентификаторов является частью выражения;
столбец идентификаторов получен из удаленного источника данных.

Если любое из этих условий выполняется, столбец создается как NOT NULL и не наследует свойство IDENTITY. Если в новой таблице необходим столбец идентификаторов, но такой столбец недоступен или необходимо изменить начальное значение или шаг приращения по сравнению с исходным столбцом идентификаторов, определите столбец в списке выбора с помощью функции IDENTITY. См. подраздел «Создание столбца идентификаторов с помощью функции IDENTITY» далее в разделе «Примеры».

Замечания

Инструкция SELECT...INTO работает в два этапа — создается новая таблица, затем вставляются строки. Это означает, что если произойдет сбой операций вставки, все они откатываются, но новая таблица остается (пустая). Если вам нужно гарантировать успех или неуспех всей операции целиком, используйте явную транзакцию.

Хранилище в Microsoft Fabric не поддерживает файловые группы. Ссылки и примеры в этой статье для файловых групп не применяются к хранилищу в Microsoft Fabric.

Ограничения

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

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

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

Указание предложения ORDER BY не гарантирует, что строки будут вставлены в указанном порядке.

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

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

Режим ведения журнала

Объем информации, записываемой в журнал для операции SELECT...INTO, зависит от модели восстановления, действующей для базы данных. В модели восстановления с неполным протоколированием и в простой модели массовые операции минимально протоколируются. При минимальном ведении журнала использование инструкции SELECT...INTO может оказаться более эффективным, чем создание таблицы и заполнение ее инструкцией INSERT. Дополнительные сведения см. в статье Журнал транзакций (SQL Server).

Инструкции SELECT...INTO, содержащие определяемые пользователем функции (UDF), являются полностью протоколируемыми операциями. Если определяемые пользователем функции, используемые в инструкции SELECT...INTO, не выполняют никакие операции доступа к данным, для таких функций можно указать предложение SCHEMABINDING, которое будет устанавливать для производного свойства UserDataAccess значение 0. После этого изменения инструкции SELECT...INTO будут протоколироваться на минимальном уровне. Если инструкция SELECT...INTO ссылается хотя бы на одну определяемую пользователем функцию, для которой это свойство имеет значение 1, операция полностью протоколируется.

Разрешения

Требует разрешения CREATE TABLE в базе данных и разрешения ALTER на схему, в которой создается таблица.

Примеры

А. Создание таблицы путем указания столбцов из нескольких источников

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

SELECT c.FirstName, c.LastName, e.JobTitle, a.AddressLine1, a.City,   
    sp.Name AS [State/Province], a.PostalCode  
INTO dbo.EmployeeAddresses  
FROM Person.Person AS c  
    JOIN HumanResources.Employee AS e   
    ON e.BusinessEntityID = c.BusinessEntityID  
    JOIN Person.BusinessEntityAddress AS bea  
    ON e.BusinessEntityID = bea.BusinessEntityID  
    JOIN Person.Address AS a  
    ON bea.AddressID = a.AddressID  
    JOIN Person.StateProvince as sp   
    ON sp.StateProvinceID = a.StateProvinceID;  
GO

B. Вставка строк с применением минимального протоколирования

В следующем примере создается таблица dbo.NewProducts, а затем вставляются строки из таблицы Production.Product. В примере предполагается, что для модели восстановления базы данных AdventureWorks2022 задано значение FULL. Чтобы обеспечить использование минимального ведения журнала, модель восстановления базы данных AdventureWorks2022 имеет значение BULK_LOGGED до вставки строк и сброса до full после выбора... Оператор INTO. Эта процедура обеспечивает минимальное использование журнала транзакций инструкцией SELECT...INTO и ее эффективное выполнение.

ALTER DATABASE AdventureWorks2022 SET RECOVERY BULK_LOGGED;  
GO  
  
SELECT * INTO dbo.NewProducts  
FROM Production.Product  
WHERE ListPrice > $25   
AND ListPrice < $100;  
GO  
ALTER DATABASE AdventureWorks2022 SET RECOVERY FULL;  
GO

C. Создание столбца идентификаторов с помощью функции IDENTITY

В следующем примере функция IDENTITY используется для создания столбца удостоверений в новой таблице Person.USAddress в базе данных AdventureWorks2022. Это необходимо, поскольку инструкция SELECT, которая определяет таблицу, содержит соединение, и в результате свойство IDENTITY не переносится в новую таблицу. Обратите внимание, что начальное значение и шаг приращения, заданные в функции IDENTITY, отличаются от значений в столбце AddressID исходной таблицы Person.Address.

-- Determine the IDENTITY status of the source column AddressID.  
SELECT OBJECT_NAME(object_id) AS TableName, name AS column_name, 
  is_identity, seed_value, increment_value  
FROM sys.identity_columns  
WHERE name = 'AddressID';  
  
-- Create a new table with columns from the existing table Person.Address. 
-- A new IDENTITY column is created by using the IDENTITY function.  
SELECT IDENTITY (int, 100, 5) AS AddressID,   
       a.AddressLine1, a.City, b.Name AS State, a.PostalCode  
INTO Person.USAddress   
FROM Person.Address AS a  
INNER JOIN Person.StateProvince AS b 
  ON a.StateProvinceID = b.StateProvinceID  
WHERE b.CountryRegionCode = N'US';   
  
-- Verify the IDENTITY status of the AddressID columns in both tables.  
SELECT OBJECT_NAME(object_id) AS TableName, name AS column_name, 
  is_identity, seed_value, increment_value  
FROM sys.identity_columns  
WHERE name = 'AddressID';

D. Создание таблицы путем указания столбцов из удаленного источника данных

В следующем примере показаны три метода создания новой таблицы на локальном сервере из удаленного источника данных. Пример начинается с создания ссылки на удаленный источник данных. Затем задается имя связанного сервера (MyLinkServer,) в предложении FROM первой инструкции SELECT...INTO и в функции OPENQUERY второй инструкции SELECT...INTO. В третьей инструкции SELECT...INTO используется функция OPENDATASOURCE, которая непосредственно задает удаленный источник данных, не указывая имя связанного сервера.

Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

USE master;  
GO  
-- Create a link to the remote data source.   
-- Specify a valid server name for @datasrc as 'server_name' 
-- or 'server_name\instance_name'.  
EXEC sp_addlinkedserver @server = N'MyLinkServer',  
    @srvproduct = N' ',  
    @provider = N'SQLNCLI',   
    @datasrc = N'server_name',  
    @catalog = N'AdventureWorks2022';  
GO  

USE AdventureWorks2022;  
GO  
-- Specify the remote data source in the FROM clause using a four-part name   
-- in the form linked_server.catalog.schema.object.  
SELECT DepartmentID, Name, GroupName, ModifiedDate  
INTO dbo.Departments  
FROM MyLinkServer.AdventureWorks2022.HumanResources.Department  
GO  
-- Use the OPENQUERY function to access the remote data source.  
SELECT DepartmentID, Name, GroupName, ModifiedDate  
INTO dbo.DepartmentsUsingOpenQuery  
FROM OPENQUERY(MyLinkServer, 'SELECT *  
               FROM AdventureWorks2022.HumanResources.Department');   
GO  
-- Use the OPENDATASOURCE function to specify the remote data source.  
-- Specify a valid server name for Data Source using the format 
-- server_name or server_name\instance_name.  
SELECT DepartmentID, Name, GroupName, ModifiedDate  
INTO dbo.DepartmentsUsingOpenDataSource  
FROM OPENDATASOURCE('SQLNCLI',  
    'Data Source=server_name;Integrated Security=SSPI')  
    .AdventureWorks2022.HumanResources.Department;  
GO

Е. Импорт из внешней таблицы, созданной с помощью PolyBase

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

Область применения: SQL Server.

-- Import data for car drivers into SQL Server to do more in-depth analysis.  
SELECT DISTINCT   
        Insured_Customers.FirstName, Insured_Customers.LastName,   
        Insured_Customers.YearlyIncome, Insured_Customers.MaritalStatus  
INTO Fast_Customers from Insured_Customers INNER JOIN   
(  
        SELECT * FROM CarSensor_Data where Speed > 35   
) AS SensorD  
ON Insured_Customers.CustomerKey = SensorD.CustomerKey  
ORDER BY YearlyIncome;

F. Копирование данных из одной таблицы в другую и создание новой таблицы в указанной файловой группе

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