Avatar of gvamsimba
gvamsimbaFlag for United Kingdom of Great Britain and Northern Ireland asked on

Exporting Data from one table to another in SQL Server 2005

HI,
      I need to export the data from one table to another in the same database in SQL Server 2005..Can anybody suggest which way is the best to do this . There are 3 million rows in my source table. Can i use the import
and export wizard ? Many Thanks
Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
tigin44

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
PedroCGD

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Guy Hengel [angelIII / a3]

you could just do a plain insert
INSERT INTO your_dest_table ( col1, col2, col3...) 
 SELECT field1, field2, field3 ...
  FROM your_source_table
 WHERE ...

Open in new window

tigin44

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 ...
colinnaylor

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
gvamsimba

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
Raja Jegan R

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

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.