Move DB objects to another layer

... without losing data

If you want to transfer DB objects e.g. from USR to CUS layer, the database fields are deleted and recreated, data is lost. To prevent that, we can:
 * Export data, move the object and import data
 * Export the objects with IDs (WITHOUT IDs, if your Microsoft Dynamics AX version is 5.0)
 * Fake IDs for the target layer

I created jobs to "correct" the IDs:

The first job LayerMove_USRCUS has to be executed before Export of USR-Layer. It fakes the tableIds and fieldIds. The second job AfterLayerMove has to be executed after the Import into the CUS-Layer. It will correct the tableIds again (necessary). So the sequence is:
 * 1) Backup DB and Application
 * 2) Job LayerMove_USRCUS
 * 3) export USR
 * 4) delete USR Layer
 * 5) import CUS
 * 6) Job AfterLayerMove
 * 7) reimport CUS (this step is not necessary in Microsoft Dynamics AX 2009)
 * 8) synchronize database

static void LayerMove_USRCUS(Args _args) {     //if other layers needed, only the macro values need to be changed #define.layerText("USR to CUS") // from - to layer #define.minUSRid(50001) // minimum id of the from-layer #define.maxUSRid(59999) // maximum id of the from-layer #define.minCUSid(40001) // minimum id of the to-layer #define.maxCUSid(49999) // maximum id of the to-layer #define.defCUSid(40001) // default id to start with, mostly = #minCUSid SysLastValue   sysLastValue; SQLDictionary  sqlTables; SQLDictionary  sqlDictionary, sqlDictionary2; TableId        tableId, maxCUStableId; int            currentId, recentFieldId; str            fieldList; boolean        yesToAll = false, moveFields; int            answer; ;     setPrefix("Transfer tables and fields from " + #layerText); //look for the highest table id in the destination layer select firstOnly sqlDictionary order by tabId desc where sqlDictionary.fieldId == 0        && sqlDictionary.tabId  >= #minCUSid && sqlDictionary.tabId  <= #maxCUSid; maxCUStableId = sqlDictionary.tabId ? sqlDictionary.tabId : #defCUSid - 1; //loop thru all tables while select sqlTables order by name where sqlTables.fieldId == 0 {         tableId = sqlTables.tabId; //if the table or some fields are located in the source layer... moveFields = (tableId >= #minUSRid && tableId <= #maxUSRid); if (!moveFields) {             select firstOnly sqlDictionary order by fieldId desc where sqlDictionary.tabId  == tableId   && sqlDictionary.fieldId >= #minUSRid && sqlDictionary.fieldId <= #maxUSRid; if (sqlDictionary) moveFields = true; }         if (moveFields) {             setPrefix(strFmt("Table %1 (Id %2)", sqlTables.name, sqlTables.tabId)); fieldList = ""; while select sqlDictionary order by fieldId asc where sqlDictionary.TabId  == tableId   && sqlDictionary.array  == 1         && sqlDictionary.fieldId >= #minUSRid && sqlDictionary.fieldId <= #maxUSRid {                 fieldList += (fieldList ? ", " : "") + sqlDictionary.name; }             //ask for permission to change if (!yesToAll) {                 answer = Box::yesYesAllNoCancel("ok to move" +                      ((tableId >= #minUSRid && tableId <= #maxUSRid) ? " table " + sqlTables.name : "") +                     (fieldList ? " fields " + fieldList : "") +                     " from " + #layerText + "?", DialogButton::No); yesToAll = (answer == DialogButton::YesToAll); if (answer == DialogButton::Cancel) break; }             if (yesToAll || answer == DialogButton::Yes) {                 ttsbegin; //if table is located in the source layer... if (tableId >= #minUSRid && tableId <= #maxUSRid) { // move table to CUS: look if already exist in lower layer select firstOnly forUpdate sqlDictionary where sqlDictionary.tabId  == sqlTables.tabId && sqlDictionary.fieldId == 0; select firstOnly sqlDictionary2 order by tabId desc where sqlDictionary2.name   == sqlTables.name && sqlDictionary2.tabId  != sqlTables.tabId && sqlDictionary2.fieldId == 0; if (sqlDictionary2) {                         tableId = sqlDictionary2.tabId; sqlDictionary.delete; //delete also dataAreaId, recId delete_from sqlDictionary where sqlDictionary.TabId   == sqlTables.tabId && sqlDictionary.fieldId > 0                && (sqlDictionary.fieldId < #minUSRid       ||                                     sqlDictionary.fieldId > #maxUSRid); }                     else {                         maxCUStableId++; tableId = maxCUStableId; sqlDictionary.tabId = tableId; sqlDictionary.update; //change dataAreaId, recId and some other fields while select forUpdate sqlDictionary order by fieldId asc, array asc where sqlDictionary.TabId   == sqlTables.tabId && sqlDictionary.fieldId > 0                && (sqlDictionary.fieldId < #minUSRid       ||                                     sqlDictionary.fieldId > #maxUSRid) {                             sqlDictionary.tabId   = tableId; sqlDictionary.update; }                     }                      delete_from sysLastValue where sysLastValue.elementName == strFmt("T:%1", sqlTables.tabId); info(strFmt("table id changed from %1 to %2", sqlTables.tabId, tableId)); }                 //look for the highest field id of that table in destination layer select firstOnly sqlDictionary order by fieldId desc where sqlDictionary.tabId  == sqlTables.tabId && sqlDictionary.fieldId >= #minCUSid      && sqlDictionary.fieldId <= #maxCUSid; currentId = sqlDictionary.FieldId ? sqlDictionary.FieldId : #defCUSid - 1; recentFieldId = 0; //search for all fields in the source layer while select forUpdate sqlDictionary order by fieldId asc, array asc where sqlDictionary.TabId  == sqlTables.tabId && sqlDictionary.fieldId >= #minUSRid      && sqlDictionary.fieldId <= #maxUSRid {                     select forUpdate sqlDictionary2 order by fieldId desc where sqlDictionary2.TabId  == tableId               && sqlDictionary2.fieldId != sqlDictionary.fieldId && sqlDictionary2.array  == sqlDictionary.array   && sqlDictionary2.name   == sqlDictionary.name; if (sqlDictionary2) {                         if (sqlDictionary2.fieldId >= #minCUSid &&                              sqlDictionary2.fieldId <= #maxCUSid) {                             currentId = sqlDictionary2.fieldId; sqlDictionary2.delete; }                     }                      else {                         // array fields have the same fieldId if (recentFieldId != sqlDictionary.fieldId) {                             currentId++; recentFieldId = sqlDictionary.fieldId; }                     }                      info(strFmt("fieldName: %1, id changed from %2 to %3", sqlDictionary.name, sqlDictionary.fieldId, currentId)); sqlDictionary.tabId  = tableId; sqlDictionary.fieldId = currentId; sqlDictionary.update; }                 ttscommit; }         }      }      setPrefix("Transfer"); info("Transfer completed"); }

static void AfterLayerMove(Args _args) {     //if other layer needed, only the macro values need to be changed #define.layerText("CUS") // from - to layer #define.minCUSid(40001) // minimum id     #define.maxCUSid(49999) // maximum id      SQLDictionary   sqlTables; SQLDictionary  sqlDictionary; Dictionary     dictionary = new Dictionary; TableId        tableId; ;     setPrefix("Checking tables of layer " + #layerText); //loop thru all tables while select sqlTables where sqlTables.fieldId == 0 && sqlTables.tabId >= #minCUSid && sqlTables.tabId <= #maxCUSid {         tableId = dictionary.tableName2Id(sqlTables.name); if (tableId != sqlTables.tabId && tableId && sqlTables.tabId) {             ttsbegin; while select forUpdate sqlDictionary where sqlDictionary.tabId == sqlTables.tabid {                 sqlDictionary.tabId = tableId; sqlDictionary.update; }             info(strFmt("table %1 id changed from %2 to %3", sqlTables.name, sqlTables.tabId, tableId)); ttscommit; }     }      setPrefix("Transfer"); info("Transfer completed"); } Any feedback welcome.

Regards, harald (--Hghrp 03:00, 20 March 2006 (PST))

Additional links
Used this article to create a more user friendly approach to this through a wizard and fixing some additional problems.

Moving_Table_between_layers