How to convert from one type of relationship table to another type I currently have a relationship table where it's:

documentID, dataType, dataID


Where documentID is the link to the document file. The dataType is if the data is a specific type of user, for example a student, teacher, vendor, etc. (as these all have different tables). The dataID is the ID of the user. 

So for example:

documentID, dataType, dataID
12343, 2, 4323


Would be the file 12343 for the type teacher (type 2 equals teacher) and it would be for teacher with ID 4323.

What I want to do is convert this table to the format:

documentID, dataType, studentID, teacherID, vendorID, etcID...
12343, 2, -1, 4323, -1, -1, ...


The reason I want to do it this way is because the files are now going to be cross-referenced a lot more and in the majority of cases a specific document will be linked to several different users and user types. 

I could keep the original table but I would prefer to keep the inserts, updates, etc. to a single database to the document relationship table. That and a few other reasons.

Anyway is there a way to convert from one table to the other? The only possible solution I came up with is to transfer all the documentID and dataTypes in one step. Then transfer the dataID using a combination of matching document ID with dataType, and running one update per dataType. At least in theory... I haven't yet been able to make that work. Is this the only way and if so any suggestions as to the actual SQL would be appreciated. So far I have:

INSERT INTO documentRelNew (documentID) SELECT documentID FROM documentRelOld;


which works great but the following SQL line fails:

UPDATE 
    documentRelNew 
SET 
    documentRelNew.studentID=documentRelOld.dataID 
FROM 
    documentRelOld 
WHERE 
    documentRelOld.dataType=2;


It fails at the FROM portion. Yet everything I've read seems to indicate this should work. In theory I need to run this for all the different data types however I can't even get it to work for the first dataType I want to test...
You can add columns to the existing tables.  These should be initially nullable until the data is loaded.  After that you can alter the column definition to NOT NULL if appropriate.  You can also drop columns from the table definition.

The process I use is:


Add new columns to table definition.
Populate new columns. (Before using code that expects these columns to be populated.)
Alter old NOT NULL columns to NULL. (Before using code that doesn't populate these columns.)
Make new columns NOT NULL where appropriate. (After code that doesn't populate these columns has been retired. May require rerun of population scripts.)
Drop old columns.  (Can be done much later.)


These steps may be done over a period of days or weeks.  This depends on your development process.  The population script should be able to identify and populate rows created or modified by old code.