copy data from one table to another with different primary key

I have to migrate data in sqlserver from one table to another. Most of the开发者_运维百科 standalone table were possible with select and insert into sp's.

The issue is in one table from the old database there is a column with a primary key say unit.

Now this unit is divided into two parts in the new database for example 'industry' and 'resource'.i.e industry + resource together constitute the unit.

How can i go about with the migration of this.

I have done similar "Database Transformation" tasks with SQL Server Integration Services it does require that you have SQL Server Standard or Enterprise (not Express) but because it is part of SQL Server there is no additional licencing, if it isn't installed you may need to re-run the installer and select "Integration Services".

SSIS will allow you to create a data flow, aranging the data more or less how you want it. There are several tutorials on MSDN.