There I have mentioned a method to handle RecIds using SQL. I am writing this article after actually implementing it.
The need arose because we had some recalculations and data inserts required for one of our clients (almost more than 2 million records).
Last time when we had run the script to perform this task in AX, it took us 4 days to complete the whole task. So we decided to try and perform these tasks using SQL Scripts for better performance.
I am not writing that script here but I will take an example and explain you how we can handle RecIds through SQL Server.
Let us assume that we need to backup current customer master to a new table. For this purpose I created a new table named SGXCustTable with following fields:
AccountNo
Name
CreatedTime
ModifiedTime
CreatedDate
ModifiedDate
CreatedDateTime (Assuming CustTable has this property enabled)
ModifiedDateTime(Assuming CustTable has this property enabled)
CreatedTransactionId
ModifiedTransactionId
CreatedBy
ModifiedBy
DataAreaId
RecVersion
RecId
Now run the following SQL Script and you will see that the data is getting inserted properly and quickly. That is the power of SQL. You will find comments inline that will help you understand each step in SQL query.
DECLARE @NEXTVAL BIGINT, @ROWCOUNT BIGINT, @NEXTVALTRANS BIGINT
DROP TABLE #TEMP
–First insert all the required data into a temporary table with RecId as an identity field
–Note: *CreatedTime and ModifiedTime are stored as integers hence convert them to corresponding integer
— *RecId field is made as an identity field that will be used later for calculating Record Ids
— *GETDATE() when used with date field automatically converts to date and with datetime field automatically gets datetime
SELECT [ACCOUNTNUM], [NAME], [MODIFIEDTIME]=(DATEPART(hour, GETDATE()) * 3600)+(DATEPART(MINUTE, GETDATE()) * 60)+DATEPART(SECOND, GETDATE()),
[CREATEDTIME]=(DATEPART(hour, GETDATE()) * 3600)+(DATEPART(MINUTE, GETDATE()) * 60)+DATEPART(SECOND, GETDATE()),
[CREATEDDATE]=GETDATE(),[MODIFIEDDATE]=GETDATE(), [CREATEDDATETIME],[MODIFIEDDATETIME],
[CREATEDTRANSACTIONID]=0,[MODIFIEDTRANSACTIONID]=0,[CREATEDBY]=‘SUMIT’,[MODIFIEDBY]=‘SUMIT’, [DATAAREAID], [RECVERSION],
[RECID] = IDENTITY(BIGINT,0,1)
INTO #TEMP
FROM CUSTTABLE WHERE CUSTTABLE.DATAAREAID = ‘CEU’
–The Next RecId value is stored in SystemSequences Table
–Get the Next RecId and store it in a variable
–Note the TABID will be the table id into which records are being inserted
SELECT @NEXTVAL=NEXTVAL
FROM SYSTEMSEQUENCES
WITH(UPDLOCK, HOLDLOCK) WHERE ID = –1 AND TABID = 50051
–Note that this is required for ModifiedTransactionId and CreatedTransactionId
–The sequence for this is stored per company with TABID as 0 (Independent of tables) and ID = -2
—
SELECT @NEXTVALTRANS=NEXTVAL
FROM SYSTEMSEQUENCES
WITH(UPDLOCK, HOLDLOCK) WHERE TABID = 0 AND ID = –2
–Now read all the records from temporary table and insert into the required table
–Note that RecId, CreatedTransactionId and ModifiedTransactionId are made using the NextVal
–fetched from SystemSequences and Identity column
INSERT INTO SGXCUSTTABLE (ACCOUNTNO, NAME, MODIFIEDTIME, CREATEDTIME, CREATEDDATE, MODIFIEDDATE,
CREATEDDATETIME, MODIFIEDDATETIME, CREATEDTRANSACTIONID, MODIFIEDTRANSACTIONID,
CREATEDBY, MODIFIEDBY, DATAAREAID, RECVERSION, RECID)
SELECT [ACCOUNTNUM], [NAME], [MODIFIEDTIME],[CREATEDTIME],[CREATEDDATE],[MODIFIEDDATE],[CREATEDDATETIME],
[MODIFIEDDATETIME],[CREATEDTRANSACTIONID]=@NEXTVALTRANS+[RECID],[MODIFIEDTRANSACTIONID]=@NEXTVALTRANS+[RECID],
[CREATEDBY],[MODIFIEDBY], [DATAAREAID], [RECVERSION],[RECID] = @NEXTVAL + [RECID]
FROM #TEMP
–Select the number of records inserted
SELECT @ROWCOUNT = COUNT(*) FROM #TEMP
–Update the used number sequences back to SystemSequences table
UPDATE SYSTEMSEQUENCES
SET NEXTVAL=NEXTVAL + @ROWCOUNT
WHERE ID = –1 AND TABID = 50051
UPDATE SYSTEMSEQUENCES
SET NEXTVAL=NEXTVAL + @ROWCOUNT
WHERE TABID = 0 AND ID = –2 AND DATAAREAID = ‘CEU’
SELECT * FROM SGXCUSTTABLE
