SELECT INTO TABLE: full copy of one table to another in SQL Server 2005:

I wrote one article few days back about different type of use of INSERT statement. You get can refer that article to get complete idea about how you can use INSERT statement and how you can copy data from one table to another with where condition and how you can use SELECT …… UNION ALL clause with INSERT statement at

http://ritesh-a-shah.blogspot.com/2009/03/dml-insert-with-multiple-ways-in-sql.html

But now I am going to show you one practical example of how you can copy data from one table to another table very efficiently.

Let us create one table for demo and insert some data in it.

--create one table

USE [AdventureWorks]

GO

CREATE TABLE [dbo].[Orders](

[OrderID] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[OrderDate] [datetime] NOT NULL DEFAULT (getdate())

)

ON [PRIMARY]

--insert data in above table.

Insert into Orders

SELECT 'A1000',GETDATE() UNION ALL

SELECT 'A1001',GETDATE()+1

--create table DummyOrders automatically

--and copy all the data to DummyOrders from Orders table

--based on given condition.

SELECT OrderID,OrderDate

INTO DummyOrders

FROM

Orders

WHERE OrderID='A1001'

Above query will not only copy data from one table to another table but it will create new table itself. It will save your lot of time when you need duplicate table.

Reference: Ritesh Shah