SQL SELECT INTO语句

最新推荐文章于 2024-08-12 23:03:10 发布

culuo4781

最新推荐文章于 2024-08-12 23:03:10 发布

阅读量5.6k

点赞数 6

文章标签：数据库 java mysql python sql

原文链接：https://www.sqlshack.com/sql-select-into-statement/

版权

This article will cover the SQL SELECT INTO statement including syntax, parameters and use with multiple tables, filegroups and a WHERE condition

本文将介绍SQL SELECT INTO语句，包括语法，参数以及与多个表，文件组和WHERE条件一起使用

We regularly insert data into SQL Server tables either from an application or directly in SSMS. We can insert data using the INSERT INTO statement. To do this, we should have a table already in place to insert data into it as we cannot create a table using Insert into the statement.

我们定期从应用程序或直接在SSMS中将数据插入SQL Server表中。我们可以使用INSERT INTO语句插入数据。为此，我们应该已经有一个表可以在其中插入数据，因为我们不能使用插入到语句来创建表。

We need to do the following tasks using INSERT INTO statement.

我们需要使用INSERT INTO语句执行以下任务。

Create a table structure with appropriate data types
创建具有适当数据类型的表结构
Insert data into it
将数据插入其中

But the good news is that we can do both the task together, elegantly, using the SQL SELECT INTO statement. It creates a table structure for the columns returned by Select statement.

但是好消息是，我们可以使用SQL SELECT INTO语句优雅地一起完成这两个任务。它为Select语句返回的列创建一个表结构。

Suppose we want to update many records in a table. We can use the SELECT INTO statement to create a backup table with the existing structure as of source table. Let us explore the SELECT INTO in this article.

假设我们要更新表中的许多记录。我们可以使用SELECT INTO语句创建一个备份表，该备份表具有与源表相同的现有结构。让我们探讨本文中的SELECT INTO。

SELECT INTO语句语法 (SELECT INTO statement syntax)

SELECT column1,column2...ColumnN
INTO New_table
FROM tables
[Where conditions];

SELECT INTO语句中的参数 (Parameters in the SELECT INTO Statement)

Columns list: We need to specify column we want to retrieve and insert into a new table 列列表：我们需要指定要检索的列并插入到新表中
New_table: We can specify the new table name here. SQL Server creates a new table with columns mentioned in columns list. We cannot replace an existing table using this. Table name should be unique New_table：我们可以在此处指定新表的名称。 SQL Server使用列列表中提到的列创建一个新表。我们不能使用此表替换现有表。表名应该是唯一的
Tables: It contains a table from where we want to get records. We can have multiple tables defined here as well with proper Join clause 表格：它包含一个我们要从中获取记录的表格。我们也可以在这里定义多个表以及适当的Join子句
WHERE conditions: We can filter records using Where clause. It is an optional clause WHERE条件：我们可以使用Where子句过滤记录。这是一个可选子句

Let us explore the SQL SELECT INTO statement using examples.

让我们使用示例探索SQL SELECT INTO语句。

环境： (Environment:)

In this example, we are using sample database AdventureWorks2017. Suppose we want to select records from [Employee] table and creates new table [Employee_Demo] using the SELECT INTO statement. We do want selected columns only in the new table. Let us run a select statement on Employee table with the columns we want to have.

在此示例中，我们使用的是示例数据库AdventureWorks2017。假设我们要从[Employee]表中选择记录，并使用SELECT INTO语句创建新表[Employee_Demo]。我们确实只希望在新表中选择列。让我们在Employee表上运行带有想要的列的select语句。

SELECT TOP (10) 
      [NationalIDNumber]
      ,[LoginID]
       ,[JobTitle]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[Gender]
      ,[HireDate]
      ,[VacationHours]         
  FROM [AdventureWorks2017].[HumanResources].[Employee]

Before we execute the SQL SELECT INTO statement, we can verify that Employee_Demo table does not exist in AdventureWorks2017 database using sp_help command.

在执行SQL SELECT INTO语句之前，可以使用sp_help命令验证AdventureWorks2017数据库中不存在Employee_Demo表。

sp_help '[AdventureWorks2017].[HumanResources].[Employee_Demo]'

In the following screenshot, we can see that Employee_Demo table does not exist in my database.

在以下屏幕截图中，我们可以看到Employee_Demo表在我的数据库中不存在。

Execute the following query to create new table [Employee_Demo] using the SELECT INTO statement.

使用SELECT INTO语句执行以下查询以创建新表[Employee_Demo]。

SELECT TOP (10) [NationalIDNumber]
      ,[LoginID]
       ,[JobTitle]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[Gender]
      ,[HireDate]
      , [VacationHours]  
      INTO   [AdventureWorks2017].[HumanResources].[Employee_Demo]     
  FROM [AdventureWorks2017].[HumanResources].[Employee]

We get the following output message in Azure Data Studio. You can note that the number of rows affected is 10. In the query, we selected top 10 records from an Employee table.

我们在Azure Data Studio中收到以下输出消息。您可以注意到受影响的行数为10。在查询中，我们从Employee表中选择了前10条记录。

We can access data from newly created table Employee_Demo and verify that it contains same records as of our earlier select statement.

我们可以从新创建的表Employee_Demo中访问数据，并验证它包含与先前的select语句相同的记录。

We did not specify any column properties in the SQL SELECT INTO statement. Let us compare the source and destination table columns and their properties.

我们没有在SQL SELECT INTO语句中指定任何列属性。让我们比较源表和目标表的列及其属性。

We can run sp_help ‘tablename’ command on both Employee and Employee_Demo table. I copied the output of both tables in excel ( for selected columns). In the following screenshot, you can see that column datatype and their properties are similar.

我们可以在Employee和Employee_Demo表上都运行sp_help'tablename'命令。我在excel中复制了两个表的输出（对于选定的列）。在以下屏幕截图中，您可以看到列数据类型及其属性是相似的。

Columns properties comparision for table created by SQL SELECT INTO

In the previous SQL SELECT INTO statement, we prepared a destination table (Employee_Demo) from a few columns in the source table (Employee).

在前面SQL SELECT INTO语句中，我们从源表（Employee）的几列中准备了一个目标表（Employee_Demo）。

Let us create another table with all columns in an Employee table with the following query.

让我们使用以下查询创建一个Employee表中所有列的另一个表。

SELECT * INTO [AdventureWorks2017].[HumanResources].[Employee_All] 
      from   [AdventureWorks2017].[HumanResources].[Employee]

In the output, we can see it inserted 290 rows in Employee_All table.

在输出中，我们可以看到它在Employee_All表中插入了290行。

We have verified that the SELECT INTO statement creates a new table with columns specified in the column list. It creates a similar data type as well in the destination table.

我们已经验证了SELECT INTO语句使用列列表中指定的列创建了一个新表。它也会在目标表中创建类似的数据类型。

Suppose we have primary and foreign keys in the source table. Does it create a primary key and foreign key as well similar to the source table? No, the SQL SELECT INTO statement does not create any keys in the destination table. If we want, we can define keys on the destination table. Let us verify this in the following section.

假设我们在源表中有主键和外键。是否创建与源表相似的主键和外键？否，SQL SELECT INTO语句不会在目标表中创建任何键。如果需要，可以在目标表上定义键。让我们在以下部分中对此进行验证。

In my earlier example, Employee table contains primary and foreign key defined on it. We can get a list of existing keys using the INFORMATION_SCHEMA view. Execute the following code, and it returns existing Primary and Foreign keys in Employee table.

在我之前的示例中，Employee表包含在其上定义的主键和外键。我们可以使用INFORMATION_SCHEMA视图获取现有密钥的列表。执行以下代码，它返回Employee表中现有的主键和外键。

SELECT
DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where Table_Name='Employee'
GO

We can see, Employee table contains primary and foreign keys.

我们可以看到，Employee表包含主键和外键。

Check Primary and Foreign keys in existing tables

We copied all columns in Employee_All from Employee table. We need to check Primary and Foreign key on destination table now.

我们从Employee表中复制了Employee_All中的所有列。我们现在需要检查目标表上的主键和外键。

SELECT
DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where Table_Name='Employee_All'
GO

We can see that it does not contain any key in destination table. It gives us flexibility to define keys on the destination table.

我们可以看到它在目标表中不包含任何键。它使我们可以灵活地在目标表上定义键。

Check Primary and Foreign keys in newly created table

SQL SELECT INTO –从多个表插入数据 (SQL SELECT INTO – Insert Data from Multiple Tables)

In previous examples, we created a table using the SELECT INTO statement from a single table Employee. We can also join multiple tables and use the SELECT INTO statement to create a new table with data as well. In this section, we want to join multiple tables together. In the following query, we joined the following tables together in AdventureWorks2017.

在前面的示例中，我们使用SELECT INTO语句从单个表Employee中创建了一个表。我们还可以联接多个表，并使用SELECT INTO语句创建一个包含数据的新表。在本节中，我们希望将多个表连接在一起。在以下查询中，我们在AdventureWorks2017中将以下表格连接在一起。

[HumanResources].[Employee]
[HumanResources]。[员工]
[Person].[Person]
[人]。[人]
[Person].[BusinessEntityAddress]
[人员]。[BusinessEntityAddress]
[Person].[Address]
[人员]。[地址]
[Person].[StateProvince]
[人]。[州府]
[Person].[CountryRegion]
[人员]。[国家/地区]
[Person].[PersonPhone]
[人]。[人电话]
[Person].[PhoneNumberType]
[人员]。[PhoneNumberType]
[Person].[EmailAddress]
[人员]。[电子邮件地址]

Execute the following query. It gives results from multiple tables based on join condition and columns mentioned.

执行以下查询。它根据连接条件和提到的列给出来自多个表的结果。

SELECT 
    e.[BusinessEntityID]
    ,p.[Title]
    ,p.[FirstName]
    ,p.[MiddleName]
    ,p.[LastName]
    ,p.[Suffix]
    ,e.[JobTitle]  
    ,pp.[PhoneNumber]
    ,pnt.[Name] AS [PhoneNumberType]
    ,ea.[EmailAddress]
    ,p.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode]
    ,cr.[Name] AS [CountryRegionName] 
    ,p.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Person] p
    ON p.[BusinessEntityID] = e.[BusinessEntityID]
    INNER JOIN [Person].[BusinessEntityAddress] bea 
    ON bea.[BusinessEntityID] = e.[BusinessEntityID] 
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = bea.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    LEFT OUTER JOIN [Person].[PersonPhone] pp
    ON pp.BusinessEntityID = p.[BusinessEntityID]
    LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
    ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
    LEFT OUTER JOIN [Person].[EmailAddress] ea
    ON p.[BusinessEntityID] = ea.[BusinessEntityID];
GO

Execute a query, and we get following the output of the SQL SELECT INTO statement.

执行一个查询，我们得到了SQL SELECT INTO语句的输出。

We want to create a table with data returned using the above table. Let’s execute the SQL SELECT INTO statement.

我们要创建一个表，其中包含使用上表返回的数据。让我们执行SQL SELECT INTO语句。

SELECT 
    e.[BusinessEntityID]
    ,p.[Title]
    ,p.[FirstName]
    ,p.[MiddleName]
    ,p.[LastName]
    ,p.[Suffix]
    ,e.[JobTitle]  
    ,pp.[PhoneNumber]
    ,pnt.[Name] AS [PhoneNumberType]
    ,ea.[EmailAddress]
    ,p.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode]
    ,cr.[Name] AS [CountryRegionName] 
    ,p.[AdditionalContactInfo]
    INTO [HumanResources].[Employee_JoinTables]
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Person] p
    ON p.[BusinessEntityID] = e.[BusinessEntityID]
    INNER JOIN [Person].[BusinessEntityAddress] bea 
    ON bea.[BusinessEntityID] = e.[BusinessEntityID] 
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = bea.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    LEFT OUTER JOIN [Person].[PersonPhone] pp
    ON pp.BusinessEntityID = p.[BusinessEntityID]
    LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
    ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
    LEFT OUTER JOIN [Person].[EmailAddress] ea
    ON p.[BusinessEntityID] = ea.[BusinessEntityID];
GO

It creates [HumanResources].[Employee_JoinTables] table and insert data into it. We can verify records in this table by the select statement.

它创建[HumanResources]。[Employee_JoinTables]表并将数据插入其中。我们可以通过select语句验证此表中的记录。

We can see you can join multiple tables together and creates output table using the SELECT INTO statement.

我们可以看到您可以将多个表连接在一起，并使用SELECT INTO语句创建输出表。

We do not need to take care of defining data types for destination table. If we want to create a table manually, we need to look at data type of each column and define datatype accordingly. If there is a mismatch between data types, you can get an error message like following.

我们无需为目标表定义数据类型。如果要手动创建表，则需要查看每列的数据类型并相应地定义数据类型。如果数据类型之间不匹配，则会出现如下错误消息。

Error 1: Due to a mismatch in data types

错误1：由于数据类型不匹配

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘GG’ to data type int.

消息245，第16级，状态1，第1行
将varchar值'GG'转换为数据类型int时，转换失败。

Error 2: Msg 8152, Level 16, State 30, Line 2

错误2：消息8152，级别16，状态30，第2行

String or binary data would be truncated.

字符串或二进制数据将被截断。

We do not get these errors while inserting data using the SQL SELECT INTO statement. However, we cannot insert data into existing tables using this method.

使用SQL SELECT INTO语句插入数据时，不会出现这些错误。但是，我们无法使用此方法将数据插入现有表中。

选择进入–指定文件组 (SELECT INTO – Specify Filegroup)

In previous sections, we explored that we can create a new table and insert data into it using the SQL SELECT INTO statement from existing tables. It creates a table in default Filegroup only. We cannot specify a particular Filegroup until SQL Server 2016. In SQL Server 2017, we can specify a particular Filegroup in which a new table should be created. SQL Server creates a new table in that particular Verify table Filegroup. If we do not specify any Filegroups in SQL Server 2017 and above, it creates a table in default Filegroup.

在前面的部分中，我们探讨了可以创建一个新表，并使用现有表中SQL SELECT INTO语句将数据插入该表中。它仅在默认文件组中创建一个表。在SQL Server 2016之前，我们无法指定特定的文件组。在SQL Server 2017中，我们可以指定在其中创建新表的特定文件组。 SQL Server在该特定的验证表文件组中创建一个新表。如果我们在SQL Server 2017及更高版本中未指定任何文件组，则会在默认文件组中创建一个表。

Note: We cannot specify Filegroups in the SQL SELECT INTO for SQL Server 2016 and before.

注意：我们无法在SQL Server 2016及更高版本SQL SELECT INTO中指定文件组。

Let us add a new Filegroup in AdventureWorks2017 database. Right click on the database and go to Filegroups.

让我们在AdventureWorks2017数据库中添加一个新的文件组。右键单击数据库，然后转到“ 文件组” 。

In this Filegroup page, click on Add FileGroup and specify a name for Filegroup.

在此文件组页面中，单击添加文件组并为文件组指定名称。

Now, click on Files and it lists out existing database files(data and log file), in this page, add a new datafile and specify FileGroup from the drop-down list. It should be FileGroup we created just now.

现在，单击“ 文件” ，它会列出现有的数据库文件（数据和日志文件），在此页面中，添加一个新的数据文件并从下拉列表中指定FileGroup。应该是我们刚才创建的FileGroup。

We want to create a SQL table in INSERTFILE Filegroup. We have not set this filegroup as default filegroup.

我们要在INSERTFILE Filegroup中创建一个SQL表。我们尚未将此文件组设置为默认文件组。

In the following query, you can see we specified filegroup name using ON clause. It works similar to a regular SQL SELECT INTO statement with a difference in a filegroup.

在以下查询中，您可以看到我们使用ON子句指定了文件组名称。它的工作原理与常规SQL SELECT INTO语句相似，但文件组不同。

select * into person.person_temp
ON INSERTFILE --FILEGROUP NAME
from person.person

Once the table is created, execute a sp_help command on this newly created table. In the following screenshot, we can verify that the table is located on INSERTFILE FileGroup. It is the FileGroup we created earlier.

创建表后，请在此新创建的表上执行sp_help命令。在以下屏幕截图中，我们可以验证该表是否位于INSERTFILE FileGroup上 。这是我们之前创建的FileGroup。

We can also verify this from table properties. Right-click on a table in SSMS. In the Storage section, we can see the desired FileGroup.

我们还可以从表属性中对此进行验证。右键单击SSMS中的表。在存储部分，我们可以看到所需的文件组。

使用Where条件选择 (SELECT INTO with a Where condition)

Suppose we want to create a table with a SQL SELECT INTO statement with few records in it. We can use a Where clause similar to a select statement. In the following query, we want to create a person.person_WC table for the person having last name is like %Duf%.

假设我们要使用SQL SELECT INTO语句创建一个表，并且其中包含很少的记录。我们可以使用类似于select语句的Where子句。在以下查询中，我们要为姓氏为％Duf％的人员创建一个person.person_WC表。

select * into person.person_WC
ON INSERTFILE -- Verify table Filegroup
from person.person
where Lastname like '%Duf%'

结论 (Conclusion)

In this article, we explored the SQL SELECT INTO statement and its usage scenarios. It is a useful command to create a copy of a table without specifying data types.

在本文中，我们探讨了SQL SELECT INTO语句及其使用方案。在不指定数据类型的情况下创建表的副本是很有用的命令。