Execute the following Microsoft SQL Server T-SQL script in Management Studio Query Editor demonstrate INSERT to one table SELECT from another table query.

The SELECT query should work & be tested on its own without any reference to the INSERT table.

------------

-- INSERT SELECT demo

------------

-- Create test table

USE AventureWorks2008;

GO

SELECT TOP (0) * INTO FiscalYearsSales

FROM Sales.vSalesPersonSalesByFiscalYears

GO

-- Populate new table with INSERT SELECT

INSERT FiscalYearsSales

SELECT pvt.SalesPersonID,

       pvt.FullName,

       pvt.JobTitle,

       pvt.SalesTerritory,

       pvt.[2002],

       pvt.[2003],

       pvt.[2004]

FROM   (SELECT soh.SalesPersonID,

               p.FirstName + ' ' + COALESCE(p.MiddleName, '') + ' ' +LastName

                AS FullName,

               e.JobTitle,

               st.Name

               AS

               SalesTerritory,

               soh.SubTotal,

               YEAR(DATEADD(m, 6, soh.OrderDate))

               AS  FiscalYear

        FROM   Sales.SalesPerson sp

               INNER JOIN Sales.SalesOrderHeader soh

                 ON sp.BusinessEntityID = soh.SalesPersonID

               INNER JOIN Sales.SalesTerritory st

                 ON sp.TerritoryID = st.TerritoryID

               INNER JOIN HumanResources.Employee e

                 ON soh.SalesPersonID = e.BusinessEntityID

               INNER JOIN Person.Person p

                 ON p.BusinessEntityID = sp.BusinessEntityID) AS soh

PIVOT ( SUM(SubTotal) FOR FiscalYear IN ([2002], [2003], [2004]) ) AS pvt;

GO 

-- (14 row(s) affected)

------------

Related article:

http://www.sqlusa.com/bestpractices/select-into/