Import / Export data including keys and indexes

Question

Import / Export data including keys and indexes

harm.vanderlinde

SSC-Addicted

Points: 452
More actions
November 17, 2010 at 2:11 am

#146224

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

Viewing 12 posts - 1 through 11 (of 11 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

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 SSC-Addicted Points: 452 More actions · Answer 2

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 SSC Guru Points: 148809 More actions · Answer 3

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. SSC-Forever Points: 46733 More actions · Answer 4

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 SSC-Addicted Points: 452 More actions · Answer 5

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. SSC-Forever Points: 46733 More actions · Answer 6

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 SSC Journeyman Points: 88 More actions · Answer 7

rajreddy.ms

SSC Journeyman

Points: 88

November 18, 2010 at 1:45 am

#1252317

Why don't u try Snapshot Replication?

harm.vanderlinde SSC-Addicted Points: 452 More actions · Answer 8

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 SSC Journeyman Points: 93 More actions · Answer 9

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 🙂

bjasmer Grasshopper Points: 19 More actions · Answer 10

As SSCrazy was saying, there is no need to buy a utility. SQL Server makes it quite easy to export all the tables and indexes (and triggers and foreign keys, and security settings etc) from one database to another.

1. Select the tables you'd like to export.

2. Tools--> Generate SQL Scripts

3. Go to the Options tab and select what you'd like to export (eg. indexes, triggers, security)

4. From here you can create a file with a script ready to run in another database.

5. Now, go into your NEW database, open the SQL Query Analyzer, import the file you just created and run it.

Thanks,

Brian Jasmer

buddy__a Hall of Fame Points: 3222 More actions · Answer 11

I know this is an old thread but I recently had to do this and found that the following Powershell commands to be very useful. They only get the constraints that I needed but you can make mnor mods to add any other constraints or even objects that you want.

$sql.Databases["MyDB"].Tables | % {$_.Indexes} | % {$_.script() }

$sql.Databases["MyDB"].Tables | % {$_.ForeignKeys} | % {$_.script()}

$sql.Databases["MyDB"].Tables | % {$_.Columns} | ? {$_.DefaultConstraint -ne $null} | % {$_.DefaultConstraint.script()}

Of course you will need to load the the SMO object and connect to the server:

$smo = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

$sql = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "YourServer"

These commands will output to the screen. If you want to dump the results to a file just add the following to the end of the commands above:

| Out-File -FilePath "C:\YourPath\ScriptFilename.sql"