Exporting Data from one table to another in SQL Server 2005

you could just do a plain insert

INSERT INTO your_dest_table ( col1, col2, col3...) 
 SELECT field1, field2, field3 ...
  FROM your_source_table
 WHERE ...

Select allOpen in new window

since your tables n the same database you dont need to use any export import wizard or anything else. Simply use a direct insert
INSERT INTO destinationTable (a, b, c, ...)
SELECT a,b,c, ....
FROM sourceTable
WHERE ...

You could try this for speed after scripting out the source table:

sp_rename SourceTable, DestTable

Create table SourceTable(col, col .....)

Grant select, etc. on SourceTable to users

This is my query for creating another table with different name..i have copied this create query from the source table and just changed the tablename..but it is giving error line 15 near (    Can anybody make the change to this query to make it right..


USE [MITS_Export]
GO
/****** Object:  Table [dbo].[tblCubeBroker]    Script Date: 01/29/2009 12:29:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCubeBroker](
      [datAuditDate] [datetime] NULL,
      [strAuditSource] [varchar](6) NULL,
      [strAuditUser] [varchar](6) NULL,
      [strCubeBrokerId] [varchar](10) NOT NULL,
      [strBrokerNo] [varchar](10) NULL,
      [strBrokerName] [varchar](50) NULL,
      [numBrokerGroup] [numeric](5, 0) NULL,
      [strGuaranteeBroker] [varchar](10) NULL,
      [strSoftwareHouse] [varchar](50) NULL,
      [strStatus] [varchar](5) NULL,
 CONSTRAINT [PK__tblCubeBroker__182C9B23] PRIMARY KEY CLUSTERED
(
      [strCubeBrokerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Your PK_ name needs to be changed.
It should be unique across the database.

you can use a structure like this too

SELECT * INTO destinationTable
FROM sourceTable

this syntax willl create the destination and copy the data from the source. But there will be no defaults, pk or fk relationship in the destination table.