Copy table

[micang]

Hi All,

I have a huge table (in SQL 2000) (well for me at least) 60 columns, 35 million rows.

I would like to make a copy of the table, but with all the indexes etc. i.e. an exact copy of the whole table.

Can this be done?

Many thanks in advance.

micanguk

 

[micang]

me again (don't know how to edit a post!)

I also need to move this table to another database.

 

[bborissov]

Is this a one time job or you must do that in regular bases?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[micang]

It will be a one time job.

 

[bborissov]

If so, script your table (with all constrains, indexes and other things) then change Table name in script (or execute script directly in other DB) and issue:

INSERT INTO NewDB.dbo.tableName -- Or change
SELECT * from OldDB.dbo.TableName

Other way is to use Export wizzard and choose to export that table in new DB.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[micang]

thanks bborissov

I am new to SQL so don't know how to script a database (real newbie)


the export wizrd sounds like an option, i wa snot aware that exporting it would keep all the indexes etc.

many thanks for your assistance.

micanguk

 

[bborissov]

What version of SQL Server you use?


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[micang]

It is SQL Server 2000

 

[bborissov]

From Enterprize Manager open your DB, Right Click on table and choose "All Tasks", then select Generate SQL Script
When Script Wizzard appears check every page of it and choose every option you need.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[micang]

many thanks Borislav

 

[micang]

I copied the table from one database to another, but none of the indexes where copied.

i still dont know how to copy a table to another table, exactly the same, i.e keeping all indexes etc.

any ideas?

 

[jbenson001]

As Bborissov said, in the wizard there are many tabs, click each one and choose the options you want before you export.