"Could not find server error" when using INSERT SELECT statement in SQL Server
To execute such statement you need to create LinkedServer first. In this case, you need to have LinkedServer from one RDS instance to another RDS instance.

https://aws.amazon.com/blogs/database/implement-linked-servers-with-amazon-rds-for-microsoft-sql-server/


  RDS SQL Server to RDS SQL Server
  
  For this scenario, both instances of RDS SQL Server must be private
  instances (not publicly accessible) within the same VPC. If the RDS
  SQL Server instances are publicly accessible, then you need to refer
  to the private IP of the RDS instances when creating the linked
  servers. Because you don’t have access to the private IP addresses of
  the servers, you need to use an EC2 instance in the same VPC to run
  NSLookup on the private instances of RDS. Do this for each RDS
  instance name.
  
  NSlookup privatesql.cb2xkfsffmy7.us-west-2.rds.amazonaws.com
  
  Address: 10.0.4.236
  
  From there, the code is very much like the RDS to EC2 code, except now
  you use the private IP address of the remote RDS instance.
  
  EXEC master.dbo.sp_addlinkedserver @server = N’RDSPrivate‘, @srvproduct=N”, @provider=N’SQLNCLI’, @datasrc=N’10.0.4.236′;
  
  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’RDSPrivate’,@useself=N’False’,@locallogin=NULL,@rmtuser=N'&lt;username&gt;’,@rmtpassword=‘&lt;password&gt;’;
  
  GO
  
  Just as we did preceding, use a four-part name to reference the remote
  RDS instance:
  
  SELECT * from RDSPrivate.TestDB.dbo.t1;
  
  Note one limitation for all RDS instances: If the physical server
  supporting your RDS SQL Server instance changes (such as when
  upgrading to a new version of SQL Server or changing the instance
  type), the private IP address of the RDS instance of SQL Server might
  change. This change can happen with no alerts or notifications to your
  administrative team. Thus, if the linked server connection fails, your
  first troubleshooting steps should include verifying that the private
  IP address of the RDS instance has not changed. If this risk is
  unacceptable in your environment, then AWS doesn’t recommend using the
  linked server feature with RDS SQL Server.