Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Import / Export data including keys and indexes Expand / Collapse
Author
Message
Posted Wednesday, November 17, 2010 2:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 2, 2012 3:06 AM
Points: 4, Visits: 10
Hallo,

For some time I'm working with MS SQL 2005 and 2008.

Ad the moment I'm using MS SQL 2005 management studio to move tables from database TEST to database USING. I do this by using the task options on database USING and then menu option 'Import Data'. Giving al the correct parameters and then executing the import.

The tables are copyed including the Data. BUT, The keys and indexes wich are in the TEST database tables are not copyed.

Can someone tell me wat I'm doing wrong or wat the correct way is for this Action?
Please help, I'l have to do this more in the futere so I need the correct way.

Harm
Post #1021965
Posted Wednesday, November 17, 2010 2:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
The keys and indexes are part of the table definition and are not contained in the data, to obtain this information right click on the source table and click on script table --> creaat to --> new query window.

this will provide you with the script that is used to build the table and the keys and indexes.

If the indexes are not contained in this script, you may need to check the option under..
tools--> options --> sql server option explorer --> scripting..


Apply the relevant part of the scripts to the destination table
Post #1021988
Posted Wednesday, November 17, 2010 2:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 2, 2012 3:06 AM
Points: 4, Visits: 10
Thanks for your reaction.

But I'm copying about 200 tables, and time is not my biggest resource.

In wat way can I copy the tables including table definition from database TEST to database USING ?

Harm.
Post #1021998
Posted Wednesday, November 17, 2010 3:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 5,229, Visits: 9,448
Harm

Some third-party backup utilities, such as LiteSpeed, include object level restore, allowing you to back up one database and restore only the objects you require into another.

If you don't have any of those products, you could search this site or elsewhere for scripts that will generate CREATE INDEX statements for the tables you are copying. Remember to test them thoroughly before you use them in a live environment.

John
Post #1022007
Posted Wednesday, November 17, 2010 3:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
harm.vanderlinde (11/17/2010)
Thanks for your reaction.

But I'm copying about 200 tables, and time is not my biggest resource.

In wat way can I copy the tables including table definition from database TEST to database USING ?

Harm.


you can select multiple tables and do them all at once, or you use the generate scripts wizard to select multiple tables, 200 shouldn't be an issue.


or else you could as suggested use a third party tool, SQL compare from Redgate would be ideal for this,
Post #1022013
Posted Wednesday, November 17, 2010 3:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 2, 2012 3:06 AM
Points: 4, Visits: 10
So, if I understand correctley. Management studio it self doesn't have a tool to copy or move complete tables (including data and definition) and never had this abbility.

is that correct?


Post #1022019
Posted Wednesday, November 17, 2010 3:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
harm.vanderlinde (11/17/2010)
So, if I understand correctley. Management studio it self doesn't have a tool to copy or move complete tables (including data and definition) and never had this abbility.

is that correct?





no that is not correct, you can do so by using the steps i outlined.
Post #1022022
Posted Thursday, November 18, 2010 1:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 8:41 AM
Points: 1, Visits: 40
Why don't u try Snapshot Replication?
Post #1022670
Posted Thursday, November 18, 2010 2:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 2, 2012 3:06 AM
Points: 4, Visits: 10
The replication is an unknown option in MS SQL for me.

I wil look into it.
hope to find the solution there.

Thanks for the tip

Harm
Post #1022681
Posted Tuesday, November 30, 2010 2:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 23, 2012 9:47 AM
Points: 3, Visits: 17
Try a 3rd party comparison and synchronization tool, such as SQL Compare Bundle or SQL Examiner Suite. These tools can migrate schema and data changes in couple minutes.

I don't know does SQL Compare allow to migrate particular keys/indexes only or not, but SQL Examiner can migrate only selected constraints instead of synchronizing all schema differences. Anyway, I don't know, do you need it or not :)
Post #1028302
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse