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
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
CREATE TABLE [dbo].[Orders](
[OrderID] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OrderDate] [datetime] NOT NULL DEFAULT (getdate())
–insert data in above table.
Insert into Orders
SELECT ‘A1000’,GETDATE() UNION ALL
–create table DummyOrders automatically
–and copy all the data to DummyOrders from Orders table
–based on given condition.
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