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