Execute the following
T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate how to apply SELECT INTO to create empty / populated permanent / temporary tables from tables, views and queries.
The SELECT INTO operation is minimally logged, therefore it is VERY FAST!
-- SQL Server SELECT INTO - sql server select into new table
-- 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
------------
Related article: INTO Clause (Transact-SQL)
-- 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)
------------
-- 23. SELECT INTO with multiple SELECTs .
-- SELECT INTO with multiple SELECT sets. Column structure must match the first one.
SELECT * INTO SelectedProducts FROM AdventureWorks2008.Production.Product
WHERE Color = 'Yellow'
UNION ALL
SELECT * FROM AdventureWorks2008.Production.Product
WHERE Color = 'Blue'
UNION ALL
SELECT * FROM AdventureWorks2008.Production.Product
WHERE Color = 'Red'
-- (100 row(s) affected)
------------
Related articles:
INTO Clause (Transact-SQL)
SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE
http://www.sqlusa.com/bestpractices2005/selectfromsproc/
|
