Copy table to other server?

[jararaca]

Is there an easy way to copy a table from a database on one SQL Server to a database on another SQL Server? The SQL Servers are within one SQL Server Group in MMC. Also, the table does not exist at the target. I'm hoping for some command or interface (GUI?) that will create a new table at the target that is identical to the one at the source, much in the same way one would copy a file from one folder to another.

?

Thanks

 

[SQLDenis]

1 way
right click on the table(from enterprise manager) you want to copy all tasks-->export data
from there on just follow the instructions from the DTS import export wizard

another way
if you haved linked servers you can do
select * into db2.Database.dbo.CopyTable
from db1.Database.dbo.RealTable

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[macleod1021]

I too want to perform an action similar to this. In my particular case, I have a Development Server and a Testing Server. The developers will randomly add new tables to their database. When the testers update their tables, they have to manually copy the tables at the time. The problem is that occasionally a table is missed and may not be discovered until an error occurs. My ultimate goal is to have a job that will query the sysobjects to find all of the tables that the app will use (we have a consistant naming scheme that makes this easy), drop the existing Testing server tables/procedures and copy the developers over.

I have the 2 databases linked and I attempted to use the select statement above, however I get the following error.

The object name 'destinationserver.databasename.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

Any help would be appreciated.

Leeland

 

[gmmastros]

I would be careful with that approach. If I'm not mistaken, the Select Into method will not copy constraints, triggers, indexes and such to the new table.

This, too, will likely cause your application to break.

If your goal is to make the testing database be the same as the development database, you may be better off backing up the development database and restoring it to the testing database.

Of course, this is my opinion. Others may not agree.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

that's right only the data and the table will be copied, no constraints, defaults etc etc

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[macleod1021]

Unfortunately, I don't want the entire database :/ What I'm looking for is to copy only the tables that deal with our app. I've begun writing a script using DMO since that seems to be the best approach I've discovered thus far. If anyone can think of something else, please let me know. When I finish, I'll post it here for those that are looking for the same solution.

 

[SQLDenis]

you could take a look at red-gate SQl Compare (

http://www.red-gate.com/products/sql_compare/index.htm)

and SQl data compare (

http://www.red-gate.com/products/SQL_Data_Compare/index.htm)

this will script out all the objects that don't exist on one server and create a one click deployment script

If you have to do this stuff a lot then it's definately worth looking into

you can also use the 2 week fully functional trial version to test it out





Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/