I've tried searching for the answer to this one to no avail. There is no good logic behind the way this was setup. The guy does not know what he's doing, but it's what I have to work with (long story). 

I'm using SQL Server 2008R2 I need to take records from one table and transfer the data to 4 separate tables all with a one to one relationship (I know - not smart). I need to get the value from the Identity field in the first table the data is inserted into, then populate the other 3 tables with the same ID and disperse the data accordingly. for example:

OldTable: Field1, Field2, Field3, Field4

NewTable1: Identity field, Field1
NewTable2: ID, Field2
NewTable3: ID, Field3
NewTable4: ID, Field4

I'd like to handle this in a stored procedure. I'd like to do a loop, but I read that loops in SQL are inadvisable. 

```
Loop moving through each record in OldTable... (??)

INSERT INTO NewTable1
  (Field1)
Select Field1 from OldTable

   INSERT INTO NewTable2
     (ID, Field2)
   Select SCOPE_IDENTITY?, Field2 From OldTable Where OldTable.ID = ??

   etc for other 2 tables

Loop to next record in OldTable

```

I am not sure how to use SCOPE\_IDENTITY, but I have a feeling this will be involved in how I accomplish this.

Also, I'm probably going to need to setup a trigger for whenever a new record is created in NewTable1. I know, it's insanity, but I can't do anything about it, just have to work around it. 

So, I need to know 
1: the best way to initially populate the tables
2: how to make triggers for new records

The solution to 1 might involve 2.

Please help!