Moving data in multiple tables from old database to one table in new database I have two SQL Server 2008 databases. One is for my old application (full of data) and the other is for a complete re-write of my application.

I want to migrate the data, but I have no idea how to tackle it. Some of the tables have exactly the same structure, but some tables in the new database are composed from columns of multiple tables in the old database. 

Is there a way to create such a script/project/application in which I can define the data transformation while also maintain constrains?
You can use SSIS (from sql 2005 and up)to transfer data from one database.Table to anotherDatabase.Table or all tables as well.

Note: SQL Server Import and Export Wizard will give you option to save the package at the end so that you can reuse the package for subsequent data transfer.

You need to be careful with constraints on the destination database. You can temporarily disable them and then reenable then once your data load is done. Script by Aaron can be found at Temporarily disable all foreign key constraints

If the tables from source and destination have exactly same schema, then you can use bcp as well. for script refer to my answer here.