I have several hundred (currently 466, but ever growing) tables I have to copy from one server to another.
I have never had to do this before, so I'm not sure at all on how to approach it. All the tables are in the same format: Cart<Eight character customer number>
This is part of a larger project of which I am merging all these Cart<Number> tables to one Carts table, but that's a whole different question altogether.
Does anyone have a best-practice method I can use to copy all these tables over? The database names on both servers are the same, if that helps. And as I said earlier, I have the sa account so I can do whatever is necessary to get the data from A to B. Both servers are in the same server farm, as well.
Best Answer
You could use SQL Server Management Studio's "Export Data" task. Right click on the database in SSMS, select Tasks and then "Export Data". It will launch an interactive wizard that will allow to copy tables from one server to another, although you will have to recreate the indices yourself. The wizard creates a temporary SSIS package (although you can also opt to save it) and is relatively fast.