Understanding SQL Server SELECT INTO statement

Filed Under: SQL Server
SQL SERVER SELECT INTO STATEMENT

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.


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.

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.

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.


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.

Syntax:

SELECT column-names 
INTO new-table
FROM old-table;
  • column-names: The column names of the old table.
  • new-table: This table is the copy of the old table with the specified values.
  • old-table: The table from which the data is to be copied into the new table.

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


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

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.


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.

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 * from Info_backup;

Output:

SQL SERVER Table - Info_backup
SQL SERVER Table – Info_backup

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.

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.

Output:

SQL SERVER SELECT INTO-Example 4
SQL SERVER SELECT INTO-Example 4

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

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

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

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’.

Output:

SQL SERVER SELECT INTO-Example 2
SQL SERVER SELECT INTO-Example 2

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.

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

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.

Output:

SQL SERVER SELECT INTO-Example 3
SQL SERVER SELECT INTO-Example 3

Point to be Noted!

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

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.

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.


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.


References

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages