SQL - Complex Data Migration from Old Structure to New Structure

Question

Alright, so here is the interesting problem...

I want to move data from OldApplicationLogs table to two tables:

1. UserSessionLogs
2. ApplicationLogs

This is the structure of the OldApplicationLogs:

ID INT-PK
UserID INT-FK
PropertyID INT-FK
Controller VARCHAR
Action VARCHAR
EntityType VARCHAR
EntityKey VARCHAR
AbsoluteURL VARCHAR
IPAddress VARCHAR
...

And these are the new structures I'm trying to move it into:

UserSessionLogs

ID INT-PK
UserID INT-FK
PropertyID INT-FK
IPAddress INT-FK
...

ApplicationLogs

ID INT-PK
UserSessionLogID INT-FK
EntityID INT-FK
EntityKey INT
Controller VARCHAR
Action VARCHAR
RelativeURL VARCHAR
Created VARCHAR
...

Now the approach I have in mind is:

1. Treat all the unique UserID, PropertyID, IPAddress as one row of UserSessionLogs.
2. Move data from old app log structure to the new one.

Here goes the SQL statements I began to write...

SQL Codes, recommended for your eyes: http://kendtimothy.com/static/logs_so_30-12-2015.pdf

Otherwise, this is the SQL codes:

1. Map past data of each user occurrence as one user session.

   SELECT 
       ID, PropertyID, UserID, IPAddress INTO UserSessionLogs 
   FROM
       OldApplicationLogs
   GROUP BY 
       PropertyID, UserID, IPAddress
   

Question 1: Loop through the results and insert into the UserSessionLogs.

INSERT INTO UserSessionLogs (PropertyID, IPAddress, UserID, StartDate)
VALUES (UserSessionLogs.PropertyID, UserSessionLogs.IPAddress, UserSessionLogs.UserID)

2. Map past data of all app logs to the new structure.

   SELECT * 
   INTO AppLogs 
   FROM OldApplicationLogs
   

Question 2: Again, I need to loop through the results and insert into ApplicationLogs.

INSERT INTO ApplicationLogs (UserSessionLogID, [Action], Controller, Operation, Created, EntityID, EntityKey, RelativeURL)
VALUES (

Question 3: How can I get the relevant UserID where the PropertyID, UserID and IPAddress combination matches?

UserSessions.ID,
AppLogs.Action,
AppLogs.Controller,
AppLogs.Operation,
AppLogs.LoggedDate

Get the EntityID which has the same table name.

(SELECT TableName 
 FROM Entities 
 WHERE AppLogs.EntityType = Entities.TableName),

Cast the previously VARCHAR EntityKey to INT? EntityKey. Leave NULL as it is.

CAST(AppLogs.EntityKey AS INT),

Convert absolute URL (from past structure) to relative URL.

Question 4 / Suggestion: Any suggestion of a better way to do this is super welcomed :)

REPLACE(AppLogs.AbsoluteURL, 'www.myapp.com/', '')
)

I'll really appreciate the help/suggestion in this, cheers :)

Answer 1

No loops are necessary here. SQL is best when working with sets of data.

Without the full DDL for the tables this may be somewhat off.

INSERT INTO [UserSessionLogs]([PropertyID], [IPAddress], [UserID])
SELECT [PropertyID], [UserID], [IPAddress]
FROM [OldApplicationLogs]
GROUP BY [PropertyID], [UserID], [IPAddress];

INSERT INTO [ApplicationLogs]([UserSessionLogID], [Action], [Controller], [Operation], [Created], [EntityID], [EntityKey], [RelativeURL])
SELECT 
    B.[ID],
    A.[Action], 
    A.[Controller],
    A.[Operation],
    A.[LoggedDate],
    C.[TableName],
    CAST(A.[EntityKey] AS INT),
    REPLACE(A.[AbsoluteURL],'www.myapp.com/','')
FROM [OldApplicationLogs] A
INNER JOIN [UserSessionLogs] B
ON      A.[UserID] = B.[UserID]
LEFT JOIN [Entities] C
ON      A.[EntityType] = C.[TableName]

The C.[TableName] seems like it would be a VARCHAR instead of an ID per your definition of ApplicationLogs. Perhaps you meant to use EntityID instead of TableName?