datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to design an INSERT SELECT query?

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/

 

Exam Prep 70-461
Exam 70-461