Copying a Table from one database to another keeping all the existing Indexes

Question

Copying a Table from one database to another keeping all the existing Indexes

bicky1980

SSCrazy

Points: 2079
More actions
November 10, 2008 at 3:24 am

#215084

Hello
Does anyone know if there's a way to copy a table from one database to another database keeping all the existing indexes?
Thanks in advance
Bicky1980

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

krayknot SSCertifiable Points: 7998 More actions · Answer 1

bicky1980 (11/10/2008)
Hello
Does anyone know if there's a way to copy a table from one database to another database keeping all the existing indexes?
Thanks in advance
Bicky1980

Data Import/Export wizard

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Sue-651097 Say Hey Kid Points: 675 More actions · Answer 2

hi,

if you use import/export wizard, the indices are not copied because they are stored in sys table in sys.sysobjects and this table is not copied.

what you can do: script your table, run scirpt on destination database and then make a bulk insert for the data.

right mouse on database: task - generate script - with indices. chose the table you want to copy.

anybody out there with a better idea? 🙂

best wishes,

sue

Susanne

krayknot SSCertifiable Points: 7998 More actions · Answer 3

kuka_99 (11/10/2008)
hi,
if you use import/export wizard, the indices are not copied because they are stored in sys table in sys.sysobjects and this table is not copied.
what you can do: script your table, run scirpt on destination database and then make a bulk insert for the data.
right mouse on database: task - generate script - with indices. chose the table you want to copy.
anybody out there with a better idea? 🙂
best wishes,
sue

thanks for the updation kuka

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

bicky1980 SSCrazy Points: 2079 More actions · Answer 4

As far as I can see when the script is generated - The table is recreated as well as the indexes - So this doesn't really benefit me in time saving - I could just easily copy the table and create indexes again myself...Or am I missing something?

ChiragNS One Orange Chip Points: 26137 More actions · Answer 5

You are right abt time saving. If you are doing it in this manner make sure that your data does not break PrimaryKey or Clustered indexes.

You could script the table and then use ssis to import the data. This way in case you have to import data multiple times to the same table you can re-use the ssis package.

"Keep Trying"

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 6

use Microsoft's Data Publishing wizard, you can copy table from one database to another database includes schema & data.

http://www.microsoft.com/downloads/details.aspx?FamilyID=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

bicky1980 SSCrazy Points: 2079 More actions · Answer 7

I tired the Microsoft SQL Server Database Publishing wizard and again as far as I can see this again recreates the table, appends the data then recreates the indexes

Am I doing it correctly?:ermm:

Sue-651097 Say Hey Kid Points: 675 More actions · Answer 8

you can once recreate the table with the indices and then copy the data all the time with appending records.

for what reason you want to copy the table, maybe this help for answering?

Susanne

bicky1980 SSCrazy Points: 2079 More actions · Answer 9

I want to copy to another database which has a lot more space available (Held on a different hard drive) - and as this is a large table 14gb want to keep the indexes to keep the time taken to a minimum.

Sue-651097 Say Hey Kid Points: 675 More actions · Answer 10

and you will do the copy only once or many times?

if you create the table itselfs on the other database one time you can append new data from the large table to the other db (maybe with a trigger, everytime there is a new entry you trigger it to the other db)

Susanne

bicky1980 SSCrazy Points: 2079 More actions · Answer 11

For now - just the once - But I know of other instances when I will want to copy a table from one database to another - Are we summarising this as: "There is no way to copy a table with indexes to another database?" Other than recreating the table and recreating the indexes?

Sue-651097 Say Hey Kid Points: 675 More actions · Answer 12

as far as i know: yes 🙂

because the indices, PKs and FKs are stored in the sys.sysobjects table in every database and this table is not copied to the other db.

Susanne

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 13

bicky1980 (11/10/2008)
For now - just the once - But I know of other instances when I will want to copy a table from one database to another - Are we summarising this as: "There is no way to copy a table with indexes to another database?" Other than recreating the table and recreating the indexes?

I'm not sure to transfer the table from server to another server (apart from Database Publishing Wizard), but there is one option in SQL 2005, you copy / move the entire database.

in SQL 2005 : Open SQL Server Management Studio, then select database -> right Click on it -> Tasks -> Copy Database...,

Hope this may resolve your problem.

🙂

bicky1980 SSCrazy Points: 2079 More actions · Answer 14

No this wont solve the problem as I only wanted to copy the table as there are many other tables which I dont need to copy over - I have done what I needed to do, just be simply copying and re-indexing the data (I thought there would have been a faster option)

Thanks for all your help

Bicky1980