I have two SQL Server 2014 DBs with different schemas. These DBs served two distinct web application operating in the same area of interest, hence I have similar tables in these two DBs. What is the easiest way to migrate data between them? I was thinking about a Transact-SQL script. Is there a tool that could solve this task more easily?
If the migration is relatively simple or if you want to reduce the number of tools involved you can stick with a tsql script. If you want to run it on a schedule you can execute it with SQL agent as TSQL or wrap it in a stored procedure and call that from the agent. If there are different servers involved you can create a linked server.
If you like a visual tool or if the process is very complex and you do not want to write tsql scripts then SSIS is a great tool that is specialized in taking data from disparate sources, applying transformations / conversions and importing. Some people also like to use SSIS for simple tasks because of the visual design surface.
Without more details it is hard to say the best route. If I had two DB's that were very similar I would consider merging the designs to accommodate both business lines / customers and add flexibility to allow more businiess lines / customers into the same design in the future