Transfer/copy data between servers using Remote Connection

Declare @droptable table (tableName varchar(max) ) 
Declare @sql varchar(max) 
Declare @Createtable table(tableName varchar(max) ) 


--Get list of tables  that are common to both Server 1 and Server 2 of Adventures --database 
insert into @droptable (tableName) 
 Select 'Drop table Adventures.dbo.'+ name tableName  from   Server1.Adventures.sys.tables where name in (select name from  server2.Adventures.sys.tables)


--Drop tables in adventures database of Server 2 by using cursor and dynamic --------query
DECLARE db_cursor CURSOR FOR  
SELECT TableNAme 
FROM @droptable


OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @sql   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       exec(@sql) 

       FETCH NEXT FROM db_cursor INTO @Sql   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor


--Create Tsql  script to  create tables with data  in server 2 
--Select *  into  adventures.dbo.Customer  from server1.adventures.dbo.customer

Insert into @Createtable (tableName) 
 
Select  ' Select *  into  pre_stagingData.dbo.' + name + '  From server1.Adventures.dbo.'+name  TableNAme from  Server1.Adventures.sys.tables 


DECLARE db_cursor CURSOR FOR  
SELECT TableNAme 
FROM @Createtable


OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @sql   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       exec(@sql) 

       FETCH NEXT FROM db_cursor INTO @Sql   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor