The following SELECT INTO T-SQL scripts demonstrate the ad-hoc creation of tables from other tables, views, queries and stored procedures. The identity property of a column is inherited by the new table.

USE AdventureWorks2008;

-- Copy one table to another with SELECT INTO

SELECT *

INTO   dbo.NewProduct

FROM   Production.Product

-- (504 row(s) affected)

-- Query new table

SELECT TOP ( 3 ) ProductID,

                 ProductName = Name,

                 ListPrice

FROM     NewProduct

ORDER BY NEWID()       -- Random sort

/*    ProductID         ProductName                         ListPrice

      875               Racing Socks, L                     8.99

      927               LL Mountain Frame - Black, 52       249.79

      794               Road-250 Black, 48                  2443.35

*/

-- Cleanup demo

DROP TABLE dbo.NewProduct

GO

-- Create empty table from query with SELECT INTO

SELECT TOP ( 0 ) YEAR(OrderDate) AS N'Year',

                 SUM(TotalDue)   AS N'Total Order Amount'

INTO     dbo.YearlySalesStage

FROM     Sales.SalesOrderHeader

GROUP BY YEAR(OrderDate)

ORDER BY YEAR;

-- (0 row(s) affected)

-- Create a new and populated table from a query with SELECT INTO

SELECT YEAR(OrderDate) AS N'Year',

       SUM(TotalDue)   AS N'Total Order Amount'

INTO     dbo.YearlySales

FROM     Sales.SalesOrderHeader

GROUP BY YEAR(OrderDate)

ORDER BY YEAR;

--(4 row(s) affected)

GO

DROP TABLE dbo.YearlySales

DROP TABLE dbo.YearlySalesStage

GO

-- Create new table from a view with SELECT INTO

SELECT SalesPersonID,

       FullName,

       JobTitle,

       SalesTerritory,

       [2002],

       [2003],

       [2004]

INTO   dbo.SalesByFiscalYears

FROM   Sales.vSalesPersonSalesByFiscalYears

-- (14 row(s) affected)

GO

DROP TABLE dbo.SalesByFiscalYears

GO

-- SELECT INTO temporary table from a stored procedure

SELECT *

INTO   #Product900

FROM   OPENQUERY([DELLSTAR\SQL2008],

'EXECUTE [AdventureWorks2008].[dbo].[uspGetBillOfMaterials]  

                                     900, ''2004-01-01''')

-- (24 row(s) affected)

GO

-- Query newly created temp table

SELECT TOP ( 3 ) ProductAssemblyID,

                 ComponentID,

                 ComponentDesc

FROM     #Product900

ORDER BY BOMLevel,

         ComponentID

/*    ProductAssemblyID             ComponentID       ComponentDesc

      900                           324               Chain Stays

      900                           325               Decal 1

      900                           326               Decal 2

*/

DROP TABLE #Product900

GO

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