MS SQL server export problem

Question

MS SQL server export problem

erik.tsomik

SSC Rookie

Points: 47
More actions
December 5, 2007 at 9:27 am

#181065

I have a liitle problem here I am trying to export a few tables from 1 database to another. But when I do that the primary key and indexes get droped. How can I do the export so i could keep all the indexes as is

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Go into Enterprise Manager and using the Import/Export capabilites of "Data Transformation Services" to copy the tables with or without data. Will give you the opportunity to copy keys, indexes, and triggers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

Specifically, use the "Copy objects and data between SQL Server databases" option of the Import/Export Wizard or the Copy SQL Server Objects task in DTS Designer to retain indexes and constraints.

Greg

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 3

it should be noted the sql server DTS will sometimes change the metadata of a table. In other words if you have a numeric field Like (numeric (11,2)) when the DTS package creates the table it might change it to the Money data type. You can change this in transformations.

Or you can script out the table and it's indexes and so on. The create the table on the other database then export and import the data. This will ensure everything is the exact same with out messing with the transformations.