Copying (hundreds of) tables from one server to another (with SSMS)

Question

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.

Answer 1

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.

Answer 2

Here's a quick & dirty approach that only needs a linked server, in each direction, with sufficient privileges, collation compatibility, and data access enabled. You run this on the source linked server to generate the dynamic SQL that will be executed on the destination linked server.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'SELECT * INTO [database].dbo.' + QUOTENAME(name)
  + N' FROM [source_linked_server].[database].dbo.' + QUOTENAME(name) + N';'
FROM sys.tables
WHERE name LIKE N'Cart[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';

PRINT @sql; -- this will only print 8K, enough to spot check
--EXEC [destination_linked_server].master.sys.sp_executesql @sql;

Answer 3

If you want something that can be scripted to be easily re-runnable as you test this and make minor changes, check out my answer here:

Import data from one Database to another script

That answer describes using a SQLCLR stored procedure which makes use of the SqlBulkCopy class in .NET. Using that stored procedure can be done in a cursor that loops through the tables. This allows for easy editing of the process as well as accounting for new tables, or easily excluding one or more table via a WHERE condition in the cursor query.

Answer 4

You can use BCP OUT and BULK INSERT INTO the destination database. You can use this script

You can use SSIS (data Import/export) to do the data transfer as well.