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 create a new table with SELECT INTO?

Execute the following T-SQL sample scripts in Microsoft SSMS Query Editor to demonstrate how to apply SELECT INTO to create empty / populated, permanent / temporary tables from other tables, views and queries. Frequent use is to create a backup of a table.

SELECT INTO operation is minimally logged, therefore it is VERY FAST!
INSERT SELECT requires prerequisites for minimal logging.


-- SQL Server SELECT INTO - sql server select into new table - INTO clause
-- Create a populated copy of the product table in tempdb
SELECT * INTO tempdb.dbo.Product FROM AdventureWorks2008.Production.Product
-- (504 row(s) affected)

-- SELECT all persons into new table with last name starting with 'A'
SELECT ID=BusinessEntityID, FullName=FirstName + ' '+ LastName
INTO ListA
FROM AdventureWorks2008.Person.Person WHERE LEFT(LastName,1) = 'A'
------------

-- SQL select into table create - sql select into syntax - sql create table select
-- Create table from select sql server - sql select into statement
-- Select into sql server - select into temp table - create table as select
-- Insert data from one table to another table in sql - sql select into table

USE AdventureWorks2008;
SELECT * INTO dbo.SalesOrderHeader FROM Sales.SalesOrderHeader WHERE Year(OrderDate)=2004
SELECT TOP (0) SalesOrderID, OrderDate INTO SOH FROM Sales.SalesOrderHeader
SELECT * INTO SOH FROM Sales.SalesOrderHeader
SELECT * INTO SOH1 FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate)=2004
SELECT * INTO SOH2 FROM Sales.SalesOrderHeader ORDER BY SalesOrderID desc
SELECT SalesOrderID, OrderDate, SubTotal INTO SOH3 FROM Sales.SalesOrderHeader
-- SQL select into group by - transact sql group by - sql server select into table
SELECT [Year]=YEAR(OrderDate), Orders=COUNT(*)
       INTO SOH4 FROM Sales.SalesOrderHeader GROUP BY YEAR(OrderDate)
SELECT *, [CreateDate]=getdate() INTO SOH5 FROM Sales.SalesOrderHeader

-- SQL Server select into temporary table - sql server select into temp table
SELECT TotalOrders = COUNT(*) INTO #TotalOrders FROM Sales.SalesOrderHeader

SELECT ProductID = CAST(ProductID as INT), ListPrice, Color
       INTO #ProdList FROM Production.Product ORDER BY ListPrice desc

SELECT Name=CONVERT(VARCHAR(45),NULL), Age=CONVERT(INT,NULL) INTO tempdb.dbo.Person
SELECT TOP (100) * INTO POH  FROM Purchasing.PurchaseOrderHeader ORDER BY NEWID()      
SELECT TOP (0) * INTO POH1 FROM Purchasing.PurchaseOrderHeader

SELECT * INTO #spwho FROM OPENQUERY (DELLSTAR,'exec sp_who') -- select into from sproc
-- SQL select into from joined tables - sql join - sql server select into new table

SELECT soh.SalesOrderID, OrderDate, OrderQty, ProductID INTO SalesOrder

FROM Sales.SalesOrderHeader soh

INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID=sod.SalesOrderID


/* SELECT INTO creates a brand new permanent or temporary table and populates
it with the result set, if any, of the SELECT query. The SELECT INTO
statement can be used to combine data from one or more tables or views into
one new table in the same or different database. It can also be used just to
create an empty table. The IDENTITY property will carry over to the new table,
to prevent it see example further down.*/


-- SQL select into copy table - sql select into from table
-- SQL select into table create - quick overview of select into create table
USE [AdventureWorks2008]; - Create table as select table
SELECT * INTO dbo.Location FROM Production.Location
/*
(1 row(s) affected) - 1 table is created
(14 row(s) affected) - table is populated with 14 rows
*/
 
-- Script out new table using Management Studio Object Explorer
-- IDENTITY property on LocationID is inherited - No indexes or constraints created

/* CREATE TABLE [dbo].[Location](
      [LocationID] [smallint] IDENTITY(1,1) NOT NULL,
      [Name] [dbo].[Name] NOT NULL,
      [CostRate] [smallmoney] NOT NULL,
      [Availability] [decimal](8, 2) NOT NULL,
      [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY] */
-- Cleanup - sql delete table
DROP TABLE dbo.Location
------------

-- SQL Server select into variable
DECLARE @Price money, @Product varchar(32)
SELECT @Product=Name, @Price = ListPrice
FROM AdventureWorks2008.Production.Product
WHERE ProductID = 800
SELECT ProductName=@Product, ListPrice=@Price
/*    ProductName             ListPrice
      Road-550-W Yellow, 44   1120.49    */
------------

-- 1. Create an empty table with the same layout as the Sales.Currency table.

USE AdventureWorks;
-- Create an empty curreny table - SQL select into create table
-- SQL create table with select into - Create table as select sql server
SELECT * INTO Sales.NewCurrency
FROM Sales.Currency
WHERE 1 > 2 -- Condition never fulfilled
--(0 row(s) affected)
/* Equivalent code
 
CREATE TABLE [Sales].[NewCurrency](
      [CurrencyCode] [nchar](3) NOT NULL,
      [Name] [dbo].[Name] NOT NULL,
      [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
*/
 
SELECT * FROM Sales.NewCurrency
GO
-- (0 row(s) affected)
 
-- Add default for ModifiedDate
ALTER TABLE Sales.NewCurrency ADD CONSTRAINT
      DF_NewCurrency_ModifiedDate DEFAULT getdate() FOR ModifiedDate
GO
 
-- Transact sql populate table
INSERT Sales.NewCurrency (CurrencyCode, Name) VALUES ('USD', 'US Dollar')
INSERT Sales.NewCurrency (CurrencyCode, Name) VALUES ('EUR', 'EURO')
GO
 
-- SQL query table
SELECT * FROM Sales.NewCurrency
GO
/*
CurrencyCode      Name        ModifiedDate
USD               US Dollar   2008-12-18 23:50:56.290
EUR               EURO        2008-12-18 23:52:00.193
*/
 
-- MSSQL delete table
DROP TABLE Sales.NewCurrency
------------
 
-- 2. SELECT INTO use in stored procedures.
 
-- SQL select into stored procedure - SELECT INTO SQL Server
-- SQL create table from another table - SQL server create table as select
USE AdventureWorks2008;
GO
CREATE PROCEDURE sprocDailySales @Date date
AS
BEGIN
-- SELECT INTO temporary table - create table select mssql - select into temp table sql
      SELECT SalesOrderID
      INTO #SOH
      FROM Sales.SalesOrderHeader
      WHERE OrderDate = @Date
 
      SELECT      [Date]=@Date,
                  TotalQty = SUM(OrderQty),
                  TotalDollar = SUM(LineTotal)
      FROM #SOH
      INNER JOIN Sales.SalesOrderDetail sod
            ON #SOH.SalesOrderID = sod.SalesOrderID
END
GO
 
EXEC sprocDailySales '2004-02-02'
GO
/* Results
 
Date        TotalQty    TotalDollar
2004-02-02  148         49410.450000

*/

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

-- 3. SELECT INTO new table from stored procedure execution.
 

-- SQL select into from stored procedure - SQL OPENROWSET statement
-- Create table select sql server - sqlserver create table as select

SELECT *
INTO #BOM
FROM OPENROWSET ( 'SQLOLEDB','SERVER=.;Trusted_Connection=yes',
'SET FMTONLY OFF EXEC AdventureWorks.dbo.uspGetBillOfMaterials 800, ''2004-02-01''');
GO
 
-- SELECT from temporary table
SELECT * FROM #BOM ORDER BY BOMLevel, ComponentDesc
GO
/* Partial results
 
ProductAssemblyID ComponentID ComponentDesc TotalQuantity StandardCost
800               952         Chain         1.00          8.9866
800               948         Front Brakes  1.00          47.286
800               945         Front Derailleur  1.00      40.6216
*/
DROP TABLE #BOM
------------
 
-- 4. Create empty table based on the Production.Product table.
 
-- SQL create table as select
-- Creating an empty table without inheriting the identity property
-- If no CONVERT, the identity property will be set for ProductID
SELECT TOP (0) ProductID=CONVERT(int, ProductID),
ProductName=Name, ListPrice, Color
INTO dbo.testProduct
FROM Production.Product
GO
 
/* Equivalent code:
 
CREATE TABLE [dbo].[testProduct](
      [ProductID] [int] NULL,
      [ProductName] [dbo].[Name] NOT NULL,
      [ListPrice] [money] NOT NULL,
      [Color] [nvarchar](15) NULL
) ON [PRIMARY]
*/
 
-- We can insert into ProductID due to identity off
INSERT testProduct VALUES (1001, 'Rocky Mountain Bike', 900.0, 'Blue')
GO
 
-- Delete table
DROP TABLE testProduct
------------
 
-- 5. Copy table, structure & content, with select into for test and development.
 
USE tempdb;
-- SQL select * into table
-- SQL select into tempdb
SELECT * INTO EPH FROM AdventureWorks.HumanResources.EmployeePayHistory
GO
-- SQL select from new table
SELECT EmployeeID, RateChangeDate
FROM EPH
WHERE RateChangeDate BETWEEN '19971101' AND '19980105'
GO
/* Results
 
EmployeeID  RateChangeDate
3     1997-12-12 00:00:00.000
4     1998-01-05 00:00:00.000
*/
-- SQL update select into table
UPDATE EPH SET RateChangeDate = '1998-01-05 00:00:01.000'
WHERE EmployeeID = 4
  AND RateChangeDate = '19980105'
GO
 
-- SQL drop table
DROP TABLE tempdb.dbo.EPH

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

 

-- 6. Create populated copy of Sales.SalesOrderHeader and empty it with TRUNCATE.

-- The following SELECT INTO query will create a populated table copy
USE tempdb;
-- SQL SELECT INTO table
-- SQL Server CREATE TABLE as SELECT 
SELECT * INTO SOH
FROM AdventureWorks.Sales.SalesOrderHeader
ORDER BY SalesOrderID
GO
-- (31465 row(s) affected)
 
-- Empty table by truncating
TRUNCATE TABLE SOH
GO
-- Command(s) completed successfully.
 
SELECT COUNT(*) FROM SOH
-- Result: 0
------------

 

-- 7. Create a temporary table with SELECT INTO based on Product table.

-- SQL create empty temporary table - SQL Server SELECT INTO
-- SQL select into temp table - sql server select into temp table
-- SQL sequential numbering - sql server identity

-- SQL sequential update - mapping non-sequential numbers to sequential numbers
SELECT TOP (0) ID=identity(int,1,1),
               ProductID=CAST(ProductID AS INT),
               ListPrice, Color
INTO #Product
FROM Production.Product
GO
 
-- SQL insert select - populate table
-- SQL Server select into existing table
INSERT INTO #Product (ProductID, ListPrice, Color)
SELECT TOP (5) ProductID, ListPrice, Color
FROM Production.Product
WHERE ListPrice > 0.0 and Color is not NULL
GO
 
-- SQL select from temp table
SELECT * FROM #Product
GO
/* Results
 
ID    ProductID   ListPrice   Color
1     680         1431.50     Black
2     706         1431.50     Red
3     707         34.99       Red
4     708         34.99       Black
5     709         9.50        White

*/
------------
 

-- 8. Create populated table with SELECT INTO from Person.Contact.

-- select into sql server
-- create new table and populate it in one query
-- SQL use
USE tempdb;
-- SQL top function
-- SQL newid - random sorting
-- SQL 3-parts reference
-- SQL select random 5 rows
SELECT      TOP(5)
            ContactID,
            FullName = FirstName+' '+LastName,
            Email=EmailAddress
INTO Person
FROM AdventureWorks.Person.Contact
WHERE EmailPromotion = 2
ORDER BY NEWID()
 
-- SQL select all columns
SELECT *
FROM Person
/* Results
 
ContactID   FullName          Email
4303        Luke Flores       luke1@adventure-works.com
8654        Danielle Stewart  danielle26@adventure-works.com
13340       Lori Ortega       lori22@adventure-works.com
8799        Gilbert Nath      gilbert38@adventure-works.com
5487        Austin Shan       austin28@adventure-works.com
*/
-- SQL drop table
DROP TABLE Person
GO

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

-- 9. Copy table into a different database with SELECT INTO .

-- SQL copy table with select into
-- Create table as select sql server
-- Copy table from one database to another with select into
-- SQL add column to new table
USE tempdb;
 
SELECT *, CopyDate = GETDATE()
INTO Department
FROM AdventureWorks.HumanResources.Department
ORDER BY DepartmentID
GO
 
SELECT DepartmentID, Department=Name, CopyDate
FROM Department ORDER BY DepartmentID
GO
/* Partial results
 
DepartmentID            Department        CopyDate
1                       Engineering       2012-01-08 15:09:59.997
2                       Tool Design       2012-01-08 15:09:59.997
3                       Sales             2012-01-08 15:09:59.997
*/
 
-- SQL drop table
DROP TABLE Department
GO
------------
 
-- 10. SELECT INTO create table; remap dates with UPDATE statement.
 

USE tempdb;
-- SQL Server select into  
SELECT TOP 100 *
INTO PurchaseOrderHeader
FROM AdventureWorks.Purchasing.PurchaseOrderHeader
ORDER BY NEWID()
GO
 
-- SQL server create table as select
-- Remap order dates to recent dates
-- SQL multiple value assignment
DECLARE @OrderDate datetime
SET @OrderDate = getdate()
UPDATE PurchaseOrderHeader
SET @OrderDate = OrderDate = dateadd(day,-1,@OrderDate)
GO
 
SELECT TOP (5) PurchaseOrderID, VendorID,OrderDate
FROM PurchaseOrderHeader
GO
/* Results
 
PurchaseOrderID   VendorID    OrderDate
2848              12          2012-01-08 10:07:27.663
91                80          2012-01-07 10:07:27.663
2788              37          2012-01-06 10:07:27.663
1538              69          2012-01-05 10:07:27.663
3687              72          2012-01-04 10:07:27.663
*/
 
-- SQL drop table
DROP TABLE tempdb.dbo.PurchaseOrderHeader
GO

----------

-- 11. SELECT INTO existing table workaround.

-- SQL select into existing table
USE tempdb;
-- First we create a table with select into
-- SQL create table as select
SELECT PurchaseOrderID = CAST (PurchaseOrderID AS INT),
 RevisionNumber, [Status], EmployeeID, VendorID,
 ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight,
 TotalDue, ModifiedDate
INTO POH
FROM AdventureWorks.Purchasing.PurchaseOrderHeader
GO
 
-- Instead of select into, we do truncate first, then insert select
TRUNCATE TABLE POH;
-- SQL insert into select
-- SQL select into existing table
INSERT INTO POH
SELECT * FROM AdventureWorks.Purchasing.PurchaseOrderHeader
GO
 
-- SQL drop table
DROP TABLE tempdb.dbo.POH
GO

----------

-- 12. SELECT INTO table create from a complex query.

-- SQL select into from multiple tables
-- Create table with select into from complex query
USE AdventureWorks;
SELECT SalesStaff =C.LastName+', '+ C.FirstName
, ZipCode = A.PostalCode
, TotalSales='$'+CONVERT(varchar, convert(money,SUM(SOD.LineTotal)),1)
, PercentOfTotal = CAST(100.0* SUM(SOD.LineTotal) / SUM(SUM(SOD.LineTotal))               
OVER (PARTITION BY 1, 2 ) AS NUMERIC (4,1))
INTO tempdb.dbo.SalesStaffReport
FROM Person.Contact C
INNER JOIN Person.[Address] A
      ON A.AddressID = C.ContactID
INNER JOIN Sales.SalesOrderHeader SOH
      ON SOH.SalesPersonID = C.ContactID
INNER JOIN Sales.SalesOrderDetail SOD
      ON SOD.SalesOrderID = SOH.SalesOrderID
WHERE TerritoryID IS NOT NULL
GROUP BY C.FirstName, C.LastName, A.PostalCode, C.ContactID
ORDER BY SalesStaff, ZipCode
GO
-- SQL select random
SELECT TOP (5) *
FROM tempdb.dbo.SalesStaffReport
ORDER BY NEWID()
GO
/* Results:
 
SalesStaff        ZipCode     TotalSales        PercentOfTotal
Esteves, Janeth   98055       $1,827,066.71     2.3
Elliott, Shannon  98027       $3,325,092.59     4.1
Elson, Jauna      98055       $6,427,005.55     8.0
Dusza, Maciej     98027       $9,293,903.00     11.5
Dyck, Shelley     98027       $10,367,007.43    12.9
*/
-- SQL drop table
DROP TABLE tempdb.dbo.SalesStaffReport
GO
---------- 
 
-- 13. SELECT INTO new table from system procedure execution.

 

-- SQL select into temporary table
-- SQL openrowset system procedure execution
SELECT * INTO #spwho
FROM OPENROWSET ( 'SQLOLEDB','SERVER=.;Trusted_Connection=yes',
'SET FMTONLY OFF EXEC sp_who');
GO
SELECT * FROM #spwho
GO
/* Partial results
 
spid  ecid  status            loginame    hostname    blk
1     0     background        sa                      0   
2     0     background        sa                      0   
3     0     suspended         sa                      0   
*/

 

-- SQL select into create temporary table
-- SQL openquery system procedure execution
SELECT DB=DB_NAME(dbid),* INTO #splock
FROM OPENQUERY(DELLSTAR, 'EXEC sp_lock')
SELECT TOP(2) * FROM  #splock
GO
/* Results
 
DB          spid  dbid  ObjId IndId Type  Resource    Mode  Status
msdb        51    4     0     0     DB                S     GRANT
Accounting  52    23    0     0     DB                S     GRANT

*/

----------
 

-- 14. SELECT INTO new table from dynamic stored procedure execution.

 

----------
-- Dynamic stored procedure to temporary table
----------
USE tempdb;
GO
-- SQL create dynamic stored procedure
CREATE PROCEDURE DynamicSprocToTempTable
AS
  BEGIN
    DECLARE  @SQL NVARCHAR(MAX)

    SET @SQL = 'SELECT top (5) * from                 AdventureWorks.Purchasing.PurchaseOrderHeader
                ORDER BY NEWID()'
    -- Dynamic SQL
    EXEC Sp_executesql    @SQL
  END
GO
 
-- Test stored procedure
EXEC tempdb.dbo.DynamicSprocToTempTable
GO
-- Select into temporary table from OPENQUERY
-- SQL select into temp table
SELECT * INTO   #poh
FROM   Openquery(SERVERALPHA,'exec tempdb.dbo.DynamicSprocToTempTable')
GO
 
SELECT PurchaseOrderID, VendorID, OrderDate
FROM   #poh
 
/* Results
 
PurchaseOrderID  VendorID    OrderDate
2662              50          2004-05-19 00:00:00.000
2454              44          2004-05-01 00:00:00.000
2547              96          2004-05-10 00:00:00.000
901               54          2003-10-13 00:00:00.000
2675              74          2004-05-22 00:00:00.000
*/
GO
 
-- Cleanup
DROP PROCEDURE DynamicSprocToTempTable
DROP TABLE #poh

----------
 

-- 15. SELECT INTO new table with UNIQUE ID column.


USE
tempdb;
-- SQL select into with identity function
SELECT TOP (100) ID=identity(int,1,1),
                        PurchaseOrderID=convert(int,PurchaseOrderID), OrderDate
INTO POH
FROM AdventureWorks.Purchasing.PurchaseOrderHeader ORDER BY NEWID()
GO
SELECT * from POH
GO
/* Partial results
 
ID    PurchaseOrderID   OrderDate
1     447               2003-09-06 00:00:00.000
2     1881              2004-03-07 00:00:00.000
3     2519              2004-05-08 00:00:00.000
4     1404              2004-01-13 00:00:00.000
*/
DROP TABLE tempdb.dbo.POH
GO
 
-- SQL select into with row_number function
SELECT TOP (100) ID=ROW_NUMBER() OVER(ORDER BY OrderDate DESC),
                         PurchaseOrderID, OrderDate
INTO POH
FROM AdventureWorks.Purchasing.PurchaseOrderHeader
GO
SELECT * from POH
GO
/* Partial results
 
ID    PurchaseOrderID   OrderDate
1     4002              2004-10-23 00:00:00.000
2     3985              2004-09-03 00:00:00.000
3     3986              2004-09-03 00:00:00.000
4     3987              2004-09-03 00:00:00.000
5     3988              2004-09-03 00:00:00.000
*/
DROP TABLE tempdb.dbo.POH

GO

----------
 

-- 16. SELECT INTO table create is very fast.
 
-- SELECT INTO is minimally logged - inside transaction only
-- SELECT INTO not logged into the transaction log file
-- SELECT INTO table cannot be recovered with transaction log restore
-- SELECT INTO table can only be recovered after full database backup
USE AdventureWorks2008;
BEGIN TRANSACTION
SELECT * INTO ProdZ FROM Production.Product
SELECT COUNT(*) FROM ProdZ
-- Result: 504
ROLLBACK TRANSACTION
SELECT COUNT(*) FROM ProdZ
/* Msg 208, Level 16, State 1, Line 6
Invalid object name 'ProdZ'.
*/

----------
 

-- 17. SELECT INTO temporary table with GROUP BY aggregate query.


-- List bestseller products

-- SQL select into temporary table
USE Northwind;
SELECT   ProductName,
         TotalSold = Sum(Quantity)
INTO #BestSellerProducts
FROM     [Order Details] od
         INNER JOIN Products p
           ON p.ProductID = od.ProductID
GROUP BY ProductName
HAVING   Sum(Quantity) > 1.75 * (SELECT Avg(TotalSold)
                                 FROM   (SELECT   TotalSold = (Sum(Quantity))
                                         FROM     [Order Details]
                                         GROUP BY ProductID) x)
ORDER BY TotalSold DESC
GO
SELECT * FROM #BestSellerProducts
GO
/* Results
 
ProductName             TotalSold
Camembert Pierrot       1577
Raclette Courdavault    1496
Gorgonzola Telino       1397
Gnocchi di nonna Alice  1263
*/
 
-- Cleanup
DROP TABLE #BestSellerProducts
GO

----------
 

-- 18. Using SELECT INTO solution instead of deleting many (i.e. millions) rows.


-- SQL using SELECT INTO instead of huge delete
- SQL delete table
-- SQL Server large delete - SQL delete millions of rows
-- Note: this is a demo only with small tables
USE tempdb;
 
SELECT *
INTO   SOH
FROM   AdventureWorks.Sales.SalesOrderHeader
GO
-- (31464 row(s) affected)
SELECT count(* )
FROM   SOH
GO
-- 31464
 
/* We need to delete all records prior to 2004/01/01 like:
 
delete SOH where OrderDate < '2004-01-01'
 
Since DELETE is a logged operation, it may take long especially with multiple indexes.
Instead we build a brand new table with the records we need to keep
and rebuild the indexes, defaults, etc, finally we switch the table with sp_rename.
*/
-- Select into records we want to keep
SELECT *
INTO   SOHStage
FROM   SOH
WHERE  OrderDate >= '2004-01-01'
GO
-- (13950 row(s) affected)
 
-- SQL rename tables  - switch new trimmed table for current table
BEGIN TRANSACTION
EXEC sp_rename
  SOH ,
  SOHArchive
 
EXEC sp_rename
  SOHStage ,
  SOH
COMMIT TRANSACTION
GO
 
/* Messages
Caution: Changing any part of an object name could break scripts and stored procedures.
Caution: Changing any part of an object name could break scripts and stored procedures.
*/
SELECT count(* )
FROM   SOH
GO
-- 13950
 
-- Cleanup
DROP TABLE tempdb.dbo.SOH
DROP TABLE tempdb.dbo.SOHArchive
GO

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

 

-- 19. Using SELECT INTO temporary table from view.

-- Select into temporary table from view

-- Select from view into table
-- SQL currency formatting - format money
-- SQL coalesce
SELECT
       [FullName]
      ,[SalesPersonID] AS StaffID
      ,[SalesTerritory]
      ,COALESCE('$'+convert(varchar,[2002],1),'') AS [2002]
      ,COALESCE('$'+convert(varchar,[2003],1),'') AS [2003]
      ,COALESCE('$'+convert(varchar,[2004],1),'') AS [2004]
  INTO #Sales
  FROM [AdventureWorks].[Sales].[vSalesPersonSalesByFiscalYears]
  ORDER BY FullName
GO
 
SELECT * FROM #Sales
ORDER BY FullName
GO
 
/* Partial results
 
FullName                StaffID     SalesTerritory    2002
David R Campbell        283         Northwest         $1,017,402.86
Garrett R Vargas        278         Canada            $930,259.47
Jae B Pak               285         United Kingdom   
Jillian  Carson         277         Central           $2,737,537.88
José Edvaldo Saraiva    282         Canada            $2,088,491.17
Linda C Mitchell        276         Southwest         $2,260,118.45
*/
 
-- Cleanup
DROP TABLE #Sales

GO

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

 

-- 20. Using SELECT INTO for random sampling of a large table.


-- SQL Random Select - See also tablesample

-- Random sampling in T-SQL - SQL Server random row selection
SELECT TOP (2) PERCENT * INTO SOHsample
FROM AdventureWorks2008.Sales.SalesOrderHeader

-- (630 row(s) affected)

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

 

-- 21. SELECT INTO from STORED PROCEDURE EXECUTION.


-- SQL select into table create from sproc

-- T-SQL dynamic SQL OPENQUERY - QUOTENAME
/*
DATA ACCESS to current SQL Server instance can be setup the following way
exec sp_serveroption @server = 'PRODSVR\SQL2008'
      ,@optname = 'DATA ACCESS'
      ,@optvalue = 'TRUE'
 
This way, OPENQUERY can be used against current instance
(Usually OPENQUERY is used to access linked servers.)
*/
-- SQL 2008 new feature: instant assignment to @localvariable
DECLARE @DynamicQuery nvarchar(max) =
      'SELECT *
      INTO BikeBOM
      FROM OPENQUERY(' + QUOTENAME(CONVERT(sysname, @@SERVERNAME))+ ',
      ''EXECUTE [AdventureWorks2008].[dbo].[uspGetWhereUsedProductID] 400,
      ''''2003-11-21'''''')'
PRINT @DynamicQuery
/*
SELECT *
INTO BikeBOM
FROM OPENQUERY([PRODSVR\SQL2008],
'EXECUTE [AdventureWorks2008].[dbo].[uspGetWhereUsedProductID] 3,
''2003-12-01''')
*/
 
EXEC sp_executeSQL @DynamicQuery
 
 
SELECT TOP ( 5 ) *
FROM BikeBOM
ORDER BY NEWID()
GO
/* Partial results
 
ProductAssemblyID ComponentID ComponentDesc
765               826         Road-650 Black, 58
769               826         Road-650 Black, 48
823               400         LL Mountain Rear Wheel
985               823         Mountain-500 Silver, 42
983               815         Mountain-400-W Silver, 46
*/
 
-- Cleanup
DROP TABLE BikeBOM
GO
------------
 
-- 22. SELECT INTO import data from Excel worksheet with distributed query . 
 

-- T-SQL import Excel worksheet into database table
SELECT * 
INTO ContactList
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=F:\data\excel\Contact.xls', 'SELECT * FROM [Contact$]')
-- (19972 row(s) affected)

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

 

Related link:

http://msdn.microsoft.com/en-us/library/ms188029.aspx

 

 

Exam Prep 70-461
Exam 70-461