SELECT INTO

My previous blog post was about using insert in many ways. You can use SELECT INTO to add data from existing table to a new table. In this statement SQL Server will create a table for you. You just want to write the query and add INTO clause to the statement. Rest will done by SQL Server for you.

USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee to non existing table
SELECT  [BusinessEntityID],
        [LoginID],
        [OrganizationNode],
        [JobTitle],
        [BirthDate],
        [MaritalStatus],
        [Gender] 
    INTO Tmp
    FROM HumanResources.Employee
GO

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO

If you are trying to add data into a existing table by using this INTO clause, you will have a error. You can join two table and add data into new table too.

USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT  HE.[BusinessEntityID],
        HE.[LoginID],
        HE.[Gender],
        PP.[FirstName],
        PP.[NameStyle]
    INTO Tmp
    FROM [HumanResources].[Employee] AS HE
    INNER JOIN Person.Person AS PP
    ON HE.[BusinessEntityID] = PP.[BusinessEntityID]
GO

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO

And you can use conditions as well.

USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT  HE.[BusinessEntityID],
        HE.[LoginID],
        HE.[Gender],
        PP.[FirstName],
        PP.[NameStyle]
    INTO Tmp
    FROM [HumanResources].[Employee] AS HE
    INNER JOIN Person.Person AS PP
    ON HE.[BusinessEntityID] = PP.[BusinessEntityID]
    WHERE HE.[BusinessEntityID] BETWEEN 70 AND 80
GO

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO

When you are joining two tables consider about the column names too. If you are using same name to two or more columns SQL Server will occur error. Below code will generate such a error.

USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT  HE.[BusinessEntityID],
        HE.[LoginID],
        HE.[Gender],
        PP.[BusinessEntityID],
        PP.[FirstName],
        PP.[NameStyle]
    INTO Tmp
    FROM [HumanResources].[Employee] AS HE
    INNER JOIN Person.Person AS PP
    ON HE.[BusinessEntityID] = PP.[BusinessEntityID]
GO

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s