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

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

This is part of a larger project of which I am merging all these Cart 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.

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;

StackExchange Database Administrators Q#122582, answer score: 24