Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Import / Export data including keys and indexes


Import / Export data including keys and indexes

Author
Message
harm.vanderlinde
harm.vanderlinde
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
steveb.
steveb.
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2848 Visits: 7195
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
harm.vanderlinde
harm.vanderlinde
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7433 Visits: 15121
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
steveb.
steveb.
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2848 Visits: 7195
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,
harm.vanderlinde
harm.vanderlinde
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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?
steveb.
steveb.
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2848 Visits: 7195
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.
rajreddy.ms
rajreddy.ms
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 48
Why don't u try Snapshot Replication?
harm.vanderlinde
harm.vanderlinde
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
Mikhail Gerasimov
Mikhail Gerasimov
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 18
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 Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search