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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: