August 5, 2008 at 12:44 pm
If this is the wrong forum, I do apologize.
I have server01 and server02 registered under "SQL Server Management Studio Express", which I would like to export/copy tables between them. Could I write a SP to export/copy between them and how I could name them in the query. Is it like SERVERNAME.DATABASENAME.dbo.TABLENAME !? Thank you in advance.
August 5, 2008 at 2:16 pm
I can think of 2 ways:
1) using the import / export wizard
2) create a linked server and use those in your stored procedures
1) if you want to import only a couple of tables on a one-time basis, this is the easy choice. right-click on a database (where you want to import tables) and all tasks -> import data. choose source server, database provide credentials and connect to the destination and provide credentials. you can create a new table / add (or) replace records to an existing table
2) create a linked server (pointing to the source server). lets say "lnsrv". then you can go "select * into table1 from lnsrv.database.owner.tablename"
actually you could save the first one as a ssis package for future use too
August 5, 2008 at 2:21 pm
Thank you for the suggestion. I am using the "SQL Server Management Studio Express", in which when I right-click the table I do not see "Import/export" as used to from the Enterprice Manager (2000). Severs registered under it are very "independent".
August 6, 2008 at 10:39 am
Express is missing some data moving features. Certainly the ones available on right click.
I don't have Express at work (only home) so I can't check, but I think that BCP still works (http://msdn.microsoft.com/en-us/library/ms162802.aspx). If BCP is a worry or you can't get physical file access then try to export to Access or Excel and use good old copy and paste!
August 6, 2008 at 11:47 am
Thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply