Enter Data into AX directly from SQL server database

while we are entering data directly to MS SQL server Table, Dynamics AX will do not display that data because three system columns DataAreaId,RecId and recVersion will be null, there is an alternative to fill out these three system columns while intering data into AX database directly from SQL server.

below are the setups required.

1. get NextVal from SYSSequences Table for that specific Table.

2. inter the data into temp SQL server table including three system columns since tow of these columns (DataAreaId and RecVersion) are fixed it make the data entry job  easy.

3.store IdentityId + NextVal(got from the syssequences table at the begin of the code block) in RecId.

4.finaly insert the data into Dynamics AX Table in Dynamics AX Database and update SYSSequences table.

below is the code

USE [DBName]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create PROC [dbo].[sp_ImportAttendance_SQL]

AS

BEGIN

BEGIN TRY

declare     @ROWCOUNT     bigint,@NEXTVAL bigint,@NEXTVALTRANS BIGINT;

SELECT @NEXTVAL= NEXTVAL

FROM AXDB.ETISALAT_AX.DBO.SYSTEMSEQUENCES

WITH(UPDLOCK, HOLDLOCK) WHERE ID = -1 AND TABID = 50154;

IF OBJECT_ID(‘DBName..#TEMPLOG’) IS NOT NULL

DROP TABLE #TEMPLOG

SELECT ED.[EMPLID]

,ED.[EMPLNAME]

,ED.[Position]

,ED.[Department]

,ED.[Division]

,ED.[Location]

,ED.[DATE]

,CASE WHEN (datepart(HOUR,ED.TIMEIN)*3600)+(DATEPART(MINUTE,ED.TIMEIN)*60)+(DATEPART(SECOND,ED.TIMEIN))>0

THEN (datepart(HOUR,ED.TIMEIN)*3600)+(DATEPART(MINUTE,ED.TIMEIN)*60)+(DATEPART(SECOND,ED.TIMEIN)) ELSE 0 END AS [TIMEIN]

,CASE WHEN (datepart(HOUR,ED.[TIMEOUT])*3600)+(DATEPART(MINUTE,ED.[TIMEOUT])*60)+(DATEPART(SECOND,ED.[TIMEOUT]))>0

THEN (datepart(HOUR,ED.[TIMEOUT])*3600)+(DATEPART(MINUTE,ED.[TIMEOUT])*60)+(DATEPART(SECOND,ED.[TIMEOUT])) ELSE 0 END AS [TIMEOUT]

,ED.[ATTENDANCERESULT]

,CASE WHEN ED.[WFSTATUS] IS NULL THEN ED.[ATTENDANCERESULT] ELSE ED.[WFSTATUS] END AS [WFSTATUS]

,[RECID] = IDENTITY(BIGINT,1,1)

,[DATAAREAID] = ‘ETIS’

,[RECVERSION]=0

INTO #TEMPLOG

FROM [BioStar].[dbo].[VU_EmployeeAttendanceDetails] ED

left join   AXDB.ETISALAT_AX.dbo.EMPLATTENDANCE EA on ED.EMPLID = EA.EMPLID AND ED.[DATE] = EA.[DATE]

WHERE EA.EMPLID IS NULL AND EA.[DATE] IS NULL AND ED.DATE between CONVERT(DATE,dateadd(day,-15,GETDATE()),23) and CONVERT(date,getdate(),23)

–SELECT * FROM #TEMPLOG

INSERT INTO [AXDB].[ETISALAT_AX].[dbo].[EMPLATTENDANCE]

(     [EMPLID],[EMPLNAME],[POSITION],[DEPARTMENT],[DIVISION],[LOCATION],[DATE]

,[TIMEIN],[TIMEOUT],[ATTENDANCERESULT],[WFSTATUS],[DATAAREAID],[RECVERSION],[RECID])

SELECT[EMPLID],[EMPLNAME],[POSITION],[DEPARTMENT],[DIVISION],[LOCATION],[DATE]

,[TIMEIN],[TIMEOUT],[ATTENDANCERESULT],[WFSTATUS],[DATAAREAID],[RECVERSION],[RECID] = @NEXTVAL+[RECID] FROM #TEMPLOG

select @ROWCOUNT = COUNT(*) from #TEMPLOG

update AXDB.ETISALAT_AX.DBO.SYSTEMSEQUENCES set NEXTVAL = @NEXTVAL+@ROWCOUNT+300 WHERE ID = -1 AND TABID = 50154;

update AXDB.ETISALAT_AX.DBO.SYSTEMSEQUENCES set NEXTVAL = @NEXTVAL+@ROWCOUNT+300 WHERE ID = -2 AND DATAAREAID = ‘DAT’

;

END TRY

BEGIN CATCH

IF(@@ERROR <>0)

BEGIN

ROLLBACK;

EXEC uspLogError

END

END CATCH

END