了解SQL Server SELECT INTO语句

àì夳堔傛蜴生んèń 2023-02-28 06:08 8阅读 0赞

Hey, readers! Hope you all are well. In the series of [SQL SERVER][], today we will be having a look at one of the most useful and interesting concept of SQL Server — **SQL SERVER** **SELECT INTO Statement**.

嘿，读者们！ 希望大家一切都好。 在[SQL SERVER][]系列文章中，今天我们将看看SQL Server最有用和最有趣的概念之一**-SQL SERVER** **SELECT INTO语句** 。

--------------------

## SQL SERVER SELECT INTO语句的工作 **(****Working of SQL SERVER SELECT INTO statement****)** ##

`SQL Server SELECT INTO statement` helps us create a new table within the same database with all or specific records from the selected(old) table. Moreover, we can add certain restrictions to the data being copied into the new table by validating it through certain conditions.

`SQL Server SELECT INTO statement`可帮助我们使用来自selected（old）表的所有或特定记录在同一数据库内创建一个新表。 此外，我们可以通过某些条件对其进行验证，从而对要复制到新表中的数据添加某些限制。

For example:

例如：

At times, it may happen that we would want our fellow developers or database engineers to work on the same database as ours. That is, it may happen that while developing a project, we would want our junior developers to work on a portion of the database.

有时，我们可能希望我们的开发人员或数据库工程师与我们在同一个数据库上工作。 也就是说，在开发项目时，我们可能希望初级开发人员在数据库的一部分上工作。

Moreover, we would also want to grant them entire access to any kind of alteration in the given database.

此外，我们还希望授予他们对给定数据库中任何种类的更改的完整访问权限。

This is when SQL SERVER SELECT INTO comes into picture.

这是SQL SERVER SELECT INTO出现的时候。

By this, a new table gets created in the database with the columns and data values from the selected/old table.

这样，数据库中就会创建一个新表，其中包含来自选定/旧表的列和数据值。

Now let us understand the structure of SQL Server SELECT INTO statement in the below section.

现在，让我们在下面的部分中了解SQL Server SELECT INTO语句的结构。

--------------------

## SQL SELECT INTO语句的语法 **(****Syntax of SQL SELECT INTO statement****)** ##

SQL Server SELECT INTO statement creates a new table within the same database and copies the data from the old table into it.

SQL Server SELECT INTO语句在同一数据库中创建一个新表，并将数据从旧表复制到该表中。

**Syntax:**

**句法：**

SELECT column-names 
    INTO new-table
    FROM old-table;

*  `column-names`: The column names of the old table.
    
    `column-names` ：旧表的列名。
 *  `new-table`: This table is the copy of the old table with the specified values.
    
    `new-table` ：此表是具有指定值的旧表的副本。
 *  `old-table`: The table from which the data is to be copied into the new table.
    
    `old-table` ：要将数据从中复制到新表中的表。

Now, let us implement SQL Server SELECT INTO statement with various examples.

现在，让我们使用各种示例来实现SQL Server SELECT INTO语句。

--------------------

## 通过示例实现SQL SERVER SELECT INTO **(****Implementing SQL SERVER SELECT INTO through examples****)** ##

At first, let us create a table and insert values into the table.

首先，让我们创建一个表并将值插入表中。

CREATE TABLE Info (
        id INT PRIMARY KEY,
        City VARCHAR (255) NOT NULL
    ); 
     
    INSERT INTO Info (
       id,
       City
    )
    VALUES
        (
            1,
          'Pune'
        ),
        (
            2,
          'Satara'
        ),
        (
            3,
          'California'
        );

**Output: Table — ‘Info’**

**输出：表—“信息”**

![SQL SERVER-Create Table][]

**SQL SERVER-Create Table** **SQL SERVER-创建表**

Now that we have created the table – ‘Info’ with above data values, let us now create a new table ‘Info\_backup’ with all the data values from the table – ‘Info’ in it.

现在，我们已经使用上面的数据值创建了表–'Info'，现在让我们创建一个新表'Info\_backup'，其中包含表–'Info'中的所有数据值。

--------------------

### SQL Server SELECT INTO语句复制所有数据值 **(****SQL Server SELECT INTO statement to copy all the data values****)** ###

**Example: Using SQL Server SELECT INTO statement to create a backup of Table – ‘Info’ as ‘Info\_backup’ within the same database.**

**示例：使用SQL Server SELECT INTO语句在同一数据库中创建表-“ Info”的备份作为“ Info\_backup”。**

SELECT *
    INTO Info_backup
    FROM Info;

We will view the data of the newly created table – ‘Info\_backup’ using the SELECT query as shown below–

我们将使用SELECT查询查看新创建表的数据“ Info\_backup”，如下所示–

Select * from Info_backup;

**Output:**

**输出：**

![SQL SERVER Table - Info\_backup][SQL SERVER Table - Info_backup]

**SQL SERVER Table – Info\_backup** **SQL SERVER表– Info\_backup**

--------------------

### **使用AS子句SQL SELECT INTO查询** **(****SQL SELECT INTO query with AS clause****)** ###

If we wish to change the column names of the data values that are being inserted into the new table from the old new, SELECT INTO table along with the AS clause can help us serve the purpose.

如果我们希望从新旧表中更改要插入到新表中的数据值的列名，则SELECT INTO表以及AS子句可以帮助我们达到目的。

**Example:**

**例：**

SELECT id AS info_id, city
    INTO Info_view
    FROM Info;

SELECT * from Info_view;

Here, we have changed the column name – ‘id’ to ‘info\_id’ in the newly created table – ‘Info\_view’ using the SQL AS Clause along with SELECT INTO statement.

在这里，我们使用SQL AS子句以及SELECT INTO语句将新创建的表中的列名“ id”更改为“ info\_id”“ Info\_view”。

**Output:**

**输出：**

![SQL SERVER SELECT INTO-Example 4][]

**SQL SERVER SELECT INTO-Example 4** **SQL SERVER SELECT INTO-示例4**

--------------------

### 使用SELECT INTO语句复制特定的列 **(****Copying specific columns using SELECT INTO statement****)** ###

Now, we will be referring to the below created table – ‘Stock’ for the below examples.

现在，在下面的示例中，我们将参考下面创建的表-“股票”。

CREATE TABLE Stock (
        id INT PRIMARY KEY,
        City VARCHAR (255) NOT NULL,
        Cost INT
    ); 
     
    INSERT INTO Stock (
       id,
       City,
      Cost
    ) VALUES
        (
            1,
          'Pune',
          100
        ),
        (
            2,
          'Satara',
          500
        ),
        (
            3,
          'California',
          50
        );

**Output: Table — ‘Stock’**

**输出：表-“库存”**

![SQL SERVER-Table 2][]

**SQL SERVER-Table 2** **SQL SERVER表2**

In the below example, we have copied only the few selected columns from the table – ‘Stock’ into the newly created table – ‘Stock\_view’.

在下面的示例中，我们仅将表中的几个选定列“ Stock”复制到了新创建的表“ Stock\_view”中。

**Example: Copying specific columns from the old table into the new table using SQL Server SELECT INTO statement.**

**示例：使用SQL Server SELECT INTO语句将特定的列从旧表复制到新表中。**

select id, cost
    INTO Stock_view
    From Stock;

Here, we have selected and copied the data values of the columns ‘id’ and ‘cost’ into the new table – ‘Stock\_view’ from ‘Stock’.

在这里，我们选择了“ id”和“ cost”列的数据值并将其复制到新表–“ Stock”中的“ Stock\_view”。

**Output:**

**输出：**

![SQL SERVER SELECT INTO-Example 2][]

**SQL SERVER SELECT INTO-Example 2** **SQL SERVER SELECT INTO-示例2**

--------------------

### **带WHERE子句SQL Server SELECT INTO语句** **(****SQL Server SELECT INTO statement with WHERE clause****)** ###

We can frame the SQL Server SELECT INTO statement with conditional clauses as well, such as, WHERE clause.

我们还可以使用条件子句（例如WHERE子句）来构造SQL Server SELECT INTO语句。

Thus, data values would be copied from old table to the new table depending upon the specified condition mentioned in the WHERE clause.

因此，根据WHERE子句中提到的指定条件，数据值将从旧表复制到新表。

**Example:**

**例：**

Select * 
    INTO Conditional_Stock
    From Stock
    WHERE Cost>50 and Cost<400;

The SELECT INTO statement copies all the data values from ‘Stock’ into the new table ‘Conditional\_Stock’ whose ‘cost’ value lies between 50 to 400.

SELECT INTO语句将所有数据值从“库存”复制到新表“ Conditional\_Stock”，其“成本”值介于50到400之间。

**Output:**

**输出：**

![SQL SERVER SELECT INTO-Example 3][]

**SQL SERVER SELECT INTO-Example 3**  **SQL SERVER SELECT INTO-示例3**

--------------------

## 要注意的一点！ **(****Point to be Noted!****)** ##

**‘Why do we need SQL Server SELECT INTO statement, when we have SQL Views to server the purpose?’**

**“当我们有SQL视图来服务于目的时，为什么我们需要SQL Server SELECT INTO语句？”**

This might be a question-mark for many of you, by the time you reach the end of this article.

在您到达本文结尾时，这对于您中的许多人来说可能是一个问号。

Let me help you with it.

让我帮你。

Using SQL Views, we can limit or restrict the access to the data by specifying condition. Same job can be performed by SELECT INTO Statement.

使用SQL视图，我们可以通过指定条件来限制或限制对数据的访问。 SELECT INTO语句可以执行相同的工作。

But the point to understand is that, **all the changes made in the view are reflected in the original database**. **While the changes made in the new tables created out of SELECT INTO statement do no reflect any changes in the original data**.

但是要理解的一点是， **视图中所做的所有更改都反映在原始数据库中** 。 **使用SELECT INTO语句创建的新表中所做的更改不会反映原始数据中的任何更改** 。

--------------------

## 结论 **(****Conclusion****)** ##

By this, we have come to the end of this topic. Please feel free to comment below in case, you come across any doubt.

至此，我们到了本主题的结尾。 如果您有任何疑问，请在下面发表评论。

For more such posts related to SQL, please do visit [SQL JournalDev][].

有关与SQL有关的更多此类帖子，请访问[SQL JournalDev][] 。

--------------------

## 参考资料 **(****References****)** ##

*  [SQL SERVER SELECT INTO — DOCUMENTATION][SQL SERVER SELECT INTO _ DOCUMENTATION]
    
    [SQL SERVER SELECT INTO —文档][SQL SERVER SELECT INTO _ DOCUMENTATION]

> 翻译自: [https://www.journaldev.com/41734/sql-server-select-into-statement][https_www.journaldev.com_41734_sql-server-select-into-statement]

[SQL SERVER]: https://www.journaldev.com/sql/sql-server
[SQL SERVER-Create Table]: /images/20230209/62ff521a17d4420e81441b5ed949b069.png
[SQL SERVER Table - Info_backup]: /images/20230209/d4c3e9ceac11470b898dd059b4b4a589.png
[SQL SERVER SELECT INTO-Example 4]: /images/20230209/6be71c9624934e43b787521091226e75.png
[SQL SERVER-Table 2]: /images/20230209/812c901246054cfa98deca5bf9ec4563.png
[SQL SERVER SELECT INTO-Example 2]: /images/20230209/378e551de3cf484cafb2188c393f788b.png
[SQL SERVER SELECT INTO-Example 3]: /images/20230209/fd1ec22201d64c6c9cb31328658abc31.png
[SQL JournalDev]: https://www.journaldev.com/sql
[SQL SERVER SELECT INTO _ DOCUMENTATION]: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql?view=sql-server-ver15
[https_www.journaldev.com_41734_sql-server-select-into-statement]: https://www.journaldev.com/41734/sql-server-select-into-statement