SQL SELECT INTO Statement

SQL SELECT INTO Statement

The SQL SELECT INTO statement is used to create a duplicate table of another table.

The SQL SELECT INTO statement selects or copies data with column structure from one table and inserts it into a new database table.



Sql select into statement using mssql select into temp table, sql server select into existing table, select into new table, select into new table, create table select into, select into temp table sql server, insert select into table, sql select into from, select into existing table, Select into Different Column Names, Select into Existing Table Identity Column.

SQL SELECT INTO Statement Syntax

The below syntax is used to select all column(s) and records from a table to another new table.


SELECT *
INTO newtablename
FROM table1
[WHERE Condition];

The below syntax is used to select specific column(s) and all records from a table to another new table.


SELECT column_name1, column_name2, ...column_nameN
INTO newtablename
FROM table1
[WHERE Condition];

The new table will be created with the column names and datatypes as defined in the SQL SELECT statement. You can apply new names using the SQL Alias clause.


Sample Database Table - Books

BookId BookName BookPrice RelYear DomainName AuthorName
1 Oracle Concurrency 80 2006 Programming Bala Murugan
2 Professional Oracle 135 2013 Programming Dharan
3 Simply MySql 100 2010 Database Vidyavathi
4 Easy Oracle PL/SQL Programming 185 2006 Security Ramanathan
5 Troubleshooting Oracle 84.22 2009 Performance Harish Karthik
6 SQL in a Nutshell 110 2008 Optimization Balan

SQL SELECT INTO Query Example

The following SQL SELECT statement will create a backup copy of "Books" table:


SELECT *
INTO BooksBackup2015
FROM Books;

The following SQL SELECT statement will Copy only a few columns into the new table:


SELECT BookName, AuthorName, BookPrice
INTO BooksBackup2012
FROM Books;

The following SQL SELECT statement will Copy only the "Database" domain records into the new table:


SELECT *
INTO BooksDomainBackup
FROM Books
WHERE Domain = 'Database';


Sql server select into query using copy data from one table to another, copy data between tables, create table from select, Select into Identity insert Value, Select into Multiple Tables, Select into New Table, Select into Union All.

SQL Select into Different Column Names

Select into... in SQL moves rows to an other (backup) table.

Example 1: Here is what I want to do: TableA has columns j1,j2,j3. I want to copy some rows from this table to another table TableB which has column k1,k2,k3,k4. Content of j1 to go into k1, k2 to k2 etc.

It makes no difference what the column names are as long as the data types are compatible (or can be cast in the assignment).

If the data types of the columns are different, consider casting the values. Simply experiment with little dummy tables. To minimise confusion, make sure to identify the target columns clearly. Like this:

INSERT INTO TableB (b1, b2, b3)
SELECT a1, a2, a3
FROM   TableA
WHERE  < some condition>;

Example 2: You can choose data from various tables and then use the result set to define the destination table.

SELECT p.DogId,
    p.DogName,
    p.DOB,
    pt.DogTypeId,
    pt.DogType,    
    o.OwnerId,
    o.FirstName,
    o.LastName,
    o.Phone,
    o.Email
INTO DogOwners
FROM Dogs p 
INNER JOIN DogTypesOwners pt 
ON p.DogTypeId = pt.DogTypeId 
INNER JOIN Owners o 
ON p.OwnerId = o.OwnerId;

Three tables are queried, and the results are inserted into a table called DogsTypesOwners.

I didn't want to duplicate the foreign key/primary key columns, in particular. In my situation, the foreign keys in the parent table have the same names as their primary key counterparts, and I would have gotten an error if the destination table had duplicate column names.

SELECT *
INTO PetsTypesOwners2
FROM Pets p 
INNER JOIN PetTypes pt 
ON p.PetTypeId = pt.PetTypeId 
INNER JOIN Owners o 
ON p.OwnerId = o.OwnerId;

Output:

Msg 2705, Level 16, State 3, Line 1

Each table must have its own column names. The name 'DogTypeId' appears multiple times in the table 'DogsTypesOwners2.'

If your foreign keys have different column names than your primary keys, you'll almost certainly end up with a destination table with extra columns (one for the primary key, one for the foreign key, and each containing the same values).

If you truly want to include duplicate columns with the same name, you can always use aliases in the destination table to give them a different name.

SELECT p.DogId, 
    p.OwnerId AS DogOwnerId, 
    p.DogTypeId AS PetDogTypeId,
    p.DogName,
    p.DOB,
    pt.DogTypeId,
    pt.DogType,    
    o.OwnerId,
    o.FirstName,
    o.LastName,
    o.Phone,
    o.Email
INTO DogsTypesOwners3
FROM Dogs p 
INNER JOIN DogTypes pt 
ON p.DogTypeId = pt.DogTypeId 
INNER JOIN Owners o 
ON p.OwnerId = o.OwnerId;

In this case I used column aliases to reassign the name of two columns to DogOwnerId and PetDogTypeId.


SQL Select into Existing Table Identity Column

Add the column definition before the into clause in a select into statement to describe a new IDENTITY column.

When duplicating data into a new table with SELECT INTO, it's a good idea to include an identity column as well, especially if the source data doesn't have a primary key.

Example 1: At its simplest this could be a statement like :

SELECT IDENTITY(INT,1,1) AS ID,*
INTO NewTable FROM ExistingTable;

Consider the following scenario: we have a table called ExistingTable with two columns:

Otherwise, using in Existing Table

The following code will generate a table containing the information displayed below:

SELECT IDENTITY(INT,1,1) AS ID, FirstName,Surname
INTO NewTable FROM ExistingTable;

Example 2: The definition includes the column’s precision but not its scale:

select column_list
     identity_column_name = identity(precision)
     into table_name
     from table_name

The following example creates a new table, new_discounts, from the discounts table and adds a new IDENTITY column, id_col:

select *, id_col=identity(5)
into new_discounts
from discounts

The select into statement fails if the column list has an existing IDENTITY column and you add a definition for a new IDENTITY column.


Select into Identity Value

Putting an identity column to a SELECT..INTO query is likely something I do on a regular basis.

It can be advantageous to add an identity column at the same time as duplicating data into a new table using SELECT INTO, but the origin should not be related to the database.

Example 1: I'm only placing it here as a point of reference. It's handy for looping across a temporary table.

SELECT id = IDENTITY(int,1,1)
,FirstName = pc.FirstName
,LastName = pc.LastName
INTO #person_contact
FROM Person.Contact pc
WHERE EmailPromotion = 2
ORDER BY pc.LastName

That data type is the first parameter for the IDENTITY function. It has to be nonnullable and of the data types int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0.

The seed (or starting) value is the second argument.

The increment is the third argument.

Using the ROW NUMBER window function is another option. Because of its optional division function, this can actually be more flexible in some cases. The clear distinction is that any further rows inserted to the table after that do not receive an identity value.

SELECT id = ROW_NUMBER() OVER (ORDER BY pc.LastName)
,FirstName = pc.FirstName
,LastName = pc.LastName
INTO #person_contact2
FROM Person.Contact pc
WHERE EmailPromotion = 2

Note that when utilising the IDENTITY technique to do an order by clause, Microsoft does not ensure that the identities will be in the correct order, particularly while using TOP or ROWCOUNT.

Example 2: At its simplest this could be a statement like:

SELECT IDENTITY(INT,1,1) AS ID,*
INTO NewTable FROM ExistingTable

For a more specific example if we have a table called ExistingTable which contains two columns :

If we run the following code it will create a table with the data shown below :

SELECT IDENTITY(INT,1,1) AS ID, FirstName,Surname
INTO NewTable FROM ExistingTable

SQL Select into Identity Insert

Is only used in conjunction with an INTO table clause in a SELECT query to insert an identity field into a new table.

Add the column definition before the into clause in a select into statement to define a new IDENTITY column.

Note: A table can't have more than one identity column.

Example 1: The precision of the column is included in the description, but not its scale:

select column_list
 identity_column_name = identity(precision)
 into table_name
 from table_name

The following example creates a new table, new_offer, from the offers table and adds a new IDENTITY column, id_col:

select *, id_col=identity(5)
into new_offers
from offers

If the column_list includes an existing IDENTITY column, and you add a description of a new IDENTITY column, the select into statement fails.

Example 2:

-- Create table
CREATE TABLE MyOrders3
(
    ProductName varchar(20)
);

-- Creating Copy of 'MyOrders3' with additional IDENTITY column
select IDENTITY(int, 1,1) AS Id,* INTO MyOrdersIdentity
from MyOrders3

insert into MyOrdersIdentity values ('Samsung')

select * from MyOrdersIdentity

SQL Select into Multiple Tables

Example 1: In this final instance, we'll combine two tables to make a new one. Below are the two tables. One is a categories table (which has a list of electrical device categories) and the other is a goods table (containing the list of specific products).

Table – categories

Category_id Category_name
1 Mobile
2 Headphone
3 Tablet
4 Laptop
5 Notebook
6 Phablet

Table – product

Product_id Category_id Product_name Release_date
1027 2 Bose Noise Cancelling Headphone 700 5/13/2019
1028 2 Sennheiser HD 450BT 2/4/2020
1029 2 Sony WH-1000XM3 8/15/2018
1030 2 SoundMagic ES18 1/1/2017
1021 1 Apple iPhone 11 Pro 9/20/2019
1022 1 Samsung Galaxy Note 10 Plus 8/23/2019
1035 3 Samsung Galaxy Tab S6 10/11/2019
1036 3 Microsoft Surface Pro 6/15/2017

The following query uses a LEFT JOIN between the two tables to construct a new table called "electronics." The LEFT JOIN duplicates all records from the left table and just those from the right table that match the left table.

SELECT product_name, 
category_name
INTO electronics
FROM products
LEFT JOIN categories
ON products.category_id=categories.category_id;

We can check the outcome of the query by running a select on the new table electronics.

Output:

Product_name Category_name
Bose Noise Cancelling Headphone 700 Headphone
Sennheiser HD 450BT Headphone
Sony WH-1000XM3 Headphone
SoundMagic ES18 Headphone
Apple iPhone 11 Pro Mobile
Samsung Galaxy Note 10 Plus Mobile
Samsung Galaxy Tab S6 Tablet
Microsoft Surface Pro Tablet
Lenovo Tab M8 Tablet
Dell Venue 7 Tablet
HP 7 VoiceTab Tablet
Samsung Galaxy Note 20 NULL
Microsoft Universal Foldable Keyboard NULL

Example 2: In the previous instances, we used the SELECT INTO statement to create a table from a single table, Employee. We may also join multiple tables and create a new table with data using the SELECT INTO statement. We'll connect numerous tables together in this section. In AdventureWorks2017, we used the following approach to connect the tables jointly.

[HumanResources].[Employee]
[Person].[Person]
[Person].[BusinessEntityAddress]
[Person].[Address]
[Person].[StateProvince]
[Person].[CountryRegion]
[Person].[PersonPhone]
[Person].[PhoneNumberType]
[Person].[EmailAddress]

Run the following command. Depending on the join criterion and columns specified, it returns results from many tables.

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.

Verify output of select statement

We want to make a table with the data from the previous table. Let's put the SQL SELECT INTO statement to the test.

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.

Verify number of records affected

Using the SELECT INTO method, we can connect many tables together and generate an output table.

The data types for the destination table do not need to be defined. If we wish to manually design a table, we must first determine the data type of each column and then specify the datatype correspondingly. If the data types aren't compatible, you'll get an error message like this.

Error 1: Due to a mismatch in data types

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value ‘GG’ to data type int.

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

String or binary data would be truncated.

When utilizing the SQL SELECT INTO statement to insert data, we do not encounter similar issues. This approach, however, cannot be used to put data into existing tables.


SQL Select into New Table

The SELECT INTO statement makes a new table and populates it with rows from the query.

You want to duplicate data from another table into a new table.

Note: The SELECT INTO statement does not copy constraints from the source table to the target table, such as primary keys and indexes.

Syntax:

The SELECT INTO statement that follows establishes the destination table and copies rows from the source table to the target table that fulfil the WHERE criteria:

SELECT 
    select_list
INTO 
    destination
FROM 
    source
[WHERE condition]

The WHERE clause is used to indicate which rows to duplicate from the source table if you only want to replicate partial data. Likewise, you may use the select list to define which columns from the source database should be copied to the target table.

Example: Our database has a table named product with data in the following columns: id (primary key), name, category, and price.

Id Flower_name Type Cost
105 rose flower 5.70
108 desk furniture 120.00
115 tulip flower 6.50
123 sunflower flower 7.50
145 guitar music 300.00
155 orchid flower 9.50
158 flute music 156.00

In the database, let’s create a new table named florist which will store the following columns: id, flowername, and cost. These columns come from the table product but only from the type flower.

It is important to note that we are creating a new table. The table florist doesn’t exist in this database.

The CREATE TABLE AS SELECT Structure:

You can use Build TABLE AS SELECT to create a new table from an existing one. This is a typical SQL construct. Take a look at the SQL code that follows:

CREATE TABLE florist
AS SELECT  *
FROM product
WHERE type = ’flower’;

Output:

Flower_name Type Cost
rose flower 5.70
tulip flower 6.50
sunflower flower 7.50
orchid flower 9.50

You can build a new table by copying data from another table using CREATE TABLE. In this scenario, we write AS and the SELECT query with the names of the columns (in our instance: *), and then we write FROM accompanied by the name of the table from which the data is obtained (in our example: product). Then you can use any SQL clause you want, such as WHERE, GROUP BY, HAVING, and so on.

The columns from the product table will be defined in the new florist table (id, flowername, type, and cost). The number of rows is controlled by a WHERE clause that filters the records to only return data from the flower category.


SQL Select into Temp

The SELECT INTO statement is one of the simplest ways to construct a new table and then copy the data from the source table into it.

As per previous conversation, the statement "SELECT INTO TEMP TABLE" is a mixture of two separate statements, each with its own function; by employing this statement, we can perform several functions with a single statement.

Syntax:

SELECT * | Column1,Column2...ColumnN 
INTO #TempDestinationTable
FROM Source_Table
WHERE Condition

Arguments of the SELECT INTO TEMP TABLE

Column List: The asterisk (*) can be used to produce a full temporary copy of the source table or to select certain columns from the source table.

Designation: The temporary table name to which we will construct and insert the data is referred to as the target table. The destination table might be either a local or global temporary table. We utilise a single hash (#) sign for the local temporary table and a hash (##) sign for the global temporary table.

The source table is a table that we wish to read data from.

We can attach a filter to the source table data using a where clause.

Example 1: The data from the Location table will be inserted into the #TempLocation table in the following example. To put it another way, we'll make a temporary copy of the Location table.

SELECT * INTO #TempLocation FROM Production.Location 
GO
SELECT * FROM #TempLocation
SELECT INTO statement for temporary tables

As can be seen, the SELECT INTO command creates the #TempLocation table and then populates it with data from the Location table.

The following query can be used to insert specific columns from the Location table into a temporary table :

SELECT LocationID,Name,ModifiedDate INTO #TempLocationCol FROM Production.Location 
GO
SELECT * FROM #TempLocationCol

A result set of a SELECT INTO statement

It's worth noting that the column names in the temporary and source tables are identical. We can give aliases to the source table columns in the select query to change the names of the temporary table's columns.

SELECT LocationID AS [TempLocationID],
Name AS [TempLocationName] ,ModifiedDate  AS [TempModifiedDate]
INTO #TempLocationCol FROM Production.Location 
GO
SELECT * FROM #TempLocationCol

Example 2: To copy data from any existing database into the temporary table in MySQL, first establish a temporary table named temporary Data and describe the table's columns using the clause "TEMPORARY TABLE."

CREATE TEMPORARY TABLE temporary_Data (ids INT,name VARCHAR(50));

The generated table does not appear in the list of tables, indicating that it is temporary. To show the temporary table, we now use:

SELECT * FROM temporary_Data;

Because no data has been placed into the database, the output says "Empty set," but it confirms the existence of the temporary table. To duplicate the full data of any existing table with the same amount of columns, we'll first use "INSERT INTO" to insert the data into a temporary table, then pick the current table from which we want to copy the data.

The general syntax would be like this:

INSERT INTO temporary_tabel_name SELECT * FROM existing table_name;

Following the general syntax, we will copy the data from the existing table, named, Guys into the newly created temporary table, named, “temporary_data”.

INSERT INTO temporary_Data SELECT * FROM  Employee_data;
To display the temporary table,
SELECT * FROM temporary_Data;

The data from the "Employee data" table has been moved to the "temporary Data" table. Now, if we want to copy and paste data from a particular column, such as "id" from the current table "Grocery bill" to the column "ids" of the existing temporary table, "temporary Data," we'll use the following statements.

INSERT INTO temporary_Data(ids) SELECT id FROM Grocery_bill;

To view the temporary table:

SELECT * FROM temporary_Data;

SQL Select into Union All

The UNION or UNION ALL operator aggregates the results of two or more searches into a single result set that contains all rows from all union queries. Any separate values provided by both the query on the left and right sides of the INTERSECT operand are returned by the INTERSECT operator. The EXCEPT operator returns any unique values from the left query that aren't present in the right query.

This issue occurs when the SELECT INTO command is used to build a new table from many sources and the table is joined together using the UNION, UNION ALL, INTERSECT, or EXCEPT operators.

Example 1: The INTO for the SELECT INTO goes into the first query of the set. An example of UNIONing the results from sys.dm_exec_query_stats and sys.dm_exec_query_stats into a temporary table is provided in listing 1.

SELECT plan_handle, execution_count, total_elapsed_time
INTO #stats
FROM sys.dm_exec_query_stats
UNION ALL
SELECT plan_handle, execution_count, total_elapsed_time
FROM sys.dm_exec_query_stats

Making temporary tables in this manner is simple and straightforward. I explain how to do it since I forgot for longer than I want to confess that it's possible to do it without using a sub-query.

Example 2: To demonstrate how the mistake occurs, consider the following SELECT INTO query with the UNION ALL operator :

SELECT 'FL' AS [StateCode], 'Florida' AS [StateName]
UNION ALL
SELECT 'CA' AS [StateCode], 'California' AS [StateName]
UNION ALL
SELECT 'NY' AS [StateCode], 'New York' AS [StateName]
INTO [dbo].[USStates]

Output:

Msg 196, Level 15, State 1, Line 1

SELECT INTO must be the first query in a statement containing a UNION, INTERSECT or EXCEPT operator.