DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
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
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE