May 8, 2006 at 3:12 am
Anyone know how I can easily copy selected tables from one database to another? (different servers).
In Enterprise Manager it was easy with Export/Import and resetting foreign-keys, indexes etc (which got lost!) but in 2005 Management Studio I can't find theses right-click options at all.
David Bytheway
May 8, 2006 at 6:30 am
David,
Although some will disagree with me, there is no real easy way to do this any more.
The best solution I've found is to create a linked database entry then script a table and create it on the destination server and do a:
INSERT INTO [SERVER1].[DATABASENAME].[OWNER].[TABLE1] (x,y,x) SELECT X,Y,Z FROM [SERVER2].[DATABASENAME].[OWNER].[TABLE1]
However when I did this at the weekend it had horrendous performance problems and resorted to deleting the destination table using the import/export wizard and then set up the indexes and permissions manually, that saved over 10 minutes on a 300mb table!
If you want to get your hands dirty then look into SSIS, this will copy the table over for you and preserve the permissions and indices etc but it is more complex than just running a wizard and if it's a one off transfer it's like using a hammer to crack a nut.
I'm sorry to say that I personally feel that this is one of the major features Microsoft has missed out of SQL Server 2005 and it has caused me grief from day one. However I have been forced to do it though script which is maybe what MS wanted me to do ...
Ed
May 9, 2006 at 8:00 am
There IS an easy way to do this and it works almost the same way as in 2000. The only difference is instead of right clicking on the tables folder, you have to right click on the database itself in the object explorer. There you will find the import and export menu options just like you had in 2000. You can choose specific tables to import/export, select source and destination, create transforms etc. It's all very straightforward, you just don't get the option on the lower levels of the object explorer tree, they only appear on the database object.
May 9, 2006 at 8:04 am
Tim,
Are you saying in your version you do have the ability to copy over object permissions? I've looked everywhere for this and can only find it in SSIS, if you do have the option avalible, can you post the build version os SQL Management Studio?
Thanks
Ed
May 10, 2006 at 7:19 am
No, the original post was about copying tables from one server to another in the same way that enterprise manager's "all tasks/export" context menu allows. I can't speak to the object permissions being moved.
T
February 18, 2008 at 11:12 pm
Thanks very much for your help.........I felt that its the perfect soln when I searched on and on...........
I will require your help further so kindly stay in touch.........
Thankyou again...........:P
January 7, 2013 at 8:16 am
Hi Tim,
Thanks to your post I could achieve what I wanted to do, though I am new to SQL, I could do it.
Regards
Piyush:-)
January 7, 2013 at 11:32 pm
Not a shill, but perhaps a tool would be a good idea - Redgates data compare or similar tool can handle easily and won't mess up your data, keys, etc. will even synch up and reseed identity columns...
There is surely a manual/brain damaging way to do it but...
Joe
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply