Can a RDBMS connect to a remote server, execute a select query and copy them into a local table just with SQL commands?
Sure, in SQL Server at least you can set up a linked server to other SQL Servers, Oracle, MySQL etc. and pull data from them. There are other approaches as well (such as OPENQUERY). An example would be:

SELECT * INTO dbo.localtable FROM OtherServer.database.dbo.TableName;


In Oracle I think the equivalent is database link but I confess that I have never used it.