SELECT INTO

My previous blog post was about using the insert in many ways. You can use SELECT INTO to add data from an 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 be done by SQL Server for you.
USE [AdventureWorks2012]GO
--Inserting data from HumanResources.Employee to non existing tableSELECT  [BusinessEntityID],
        [LoginID],
        [OrganizationNode],
        [JobTitle],
        [BirthDate],
        [MaritalStatus],
        [Gender] 
    INTO Tmp
    FROM HumanResources.EmployeeGO
--Retrieving dataSELECT * FROM TmpGO
--Dropping TableDROP TABLE TmpGO

If you are trying to add data into an existing table by using this INTO clause, you will have an error. You can join two tables and add data into the new table too.
USE [AdventureWorks2012]GO
--Inserting data from HumanResources.Employee and Person.Person to non existing tableSELECT  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 dataSELECT * FROM TmpGO
--Dropping TableDROP TABLE TmpGO

And you can use conditions as well.
USE [AdventureWorks2012]GO
--Inserting data from HumanResources.Employee and Person.Person to non existing tableSELECT  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 dataSELECT * FROM TmpGO
--Dropping TableDROP TABLE TmpGO

When you are joining two tables consider the column names too. If you are using the same name to two or more columns SQL Server will occur error. Below code will generate such an error.
USE [AdventureWorks2012]GO
--Inserting data from HumanResources.Employee and Person.Person to non existing tableSELECT  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 dataSELECT * FROM TmpGO
--Dropping TableDROP TABLE TmpGO