March 13, 2008 at 8:30 am
Hello,
I'm in the process of converting from SQL Server 2000 to 2005. I've just purchased the Developer Edition of 2005 in order to have access to all of the import/export functionality that I need, but I'm frustrated to find that I still can't see a way of doing what I used to be able to do quite easily in 2000....
That is, do an import/export of selected tables from one database on the server to another. That obviously involves the table structures and the data, but also the keys associated with each table. As it stands, I appear to be able to do an import/export of the table structures and data without the keys or just copy the database in its entirety, neither of which really fits the task in hand.
Any help would be much appreciated, whether it's a way to do all of this via the Management Studio or a recommendation for a third-party tool that'd do the job.
Many thanks,
ian.
March 13, 2008 at 9:26 am
The Import/Export wizard in SQL 2005 does not include the Transfer SQL Server Objects task like the SQL 2000 version did. The task is only available when you create an SSIS package in SQL Server Business Intelligence Development Studio (BIDS).
If you want to use only the wizard, you can script the primary keys, indexes, etc. and run the script before importing data with the wizard.
Greg
March 13, 2008 at 9:29 am
Right-click the database your table belongs to;
...
You cannot miss it.
March 13, 2008 at 10:10 am
Greg Charles (3/13/2008)
If you want to use only the wizard, you can script the primary keys, indexes, etc. and run the script before importing data with the wizard.
Thanks for getting back to me, both of you. Much obliged.
In nearly all instances, I want to replace the tables in their entirety, with updated versions containing both different table structures and different data. The most immediate example I can offer is that I have a development site and a live site for a content management system; both sites have two tables containing HTML template code, but the development version of the database has an expanded set of fields and a larger set of layouts. When the time comes, I want to replace those two tables in the live site with those in the development site...that is, structures, data, keys, indexes and all.
From what you've said, it sounds as if I'd actually need to delete the tables in the target database, recreate them (and their keys, indexes and so forth) using a script, and then import the data from the source database. I could do that, I guess...but it seems remarkably laborious, given that it was possible simply to copy the objects across in Enterprise Manager 2000. That's progress for ya!
It's a task that I have to do a fair amount, so I'd really like to find a more elegant and automatic way if such a thing exists. Are there any third party tools that recreate the functionality of EM 2000?
Thanks again,
ian.
March 13, 2008 at 11:16 am
Like I said, you can do this by creating an SSIS package. Since you're going to do this often, you could save the package and run it when you need.
Here's another option I just remembered: if you have the SQL 2000 DTS runtime on your server, you can still use the DTS-based Import/Export wizard. It's should be located at
Program Files\Microsoft SQL Server\80\Tools\Binn\dtswiz.exe.
Greg
March 13, 2008 at 11:53 am
Greg Charles (3/13/2008)
Here's another option I just remembered: if you have the SQL 2000 DTS runtime on your server, you can still use the DTS-based Import/Export wizard. It's should be located atProgram Files\Microsoft SQL Server\80\Tools\Binn\dtswiz.exe.
😀 Greg, you're a genius! That works perfectly well, so I can still do what I did before. Marvellous!
Many, many thanks!
ian.
March 14, 2008 at 9:16 am
Genius?:blush: I just had the same experience before you did, did the necessary digging, and wanted to pass it on. Thanks for the compliment, though!
Greg
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply