≡ Menu

How to get next recid in TSQL Dynamics Ax 2012

Suppose we have to insert data into VendGroup table through TSQL, this scenario usually appear when integration or data migration team inject data into AX using TSQL without using AIF.

There are two tables are important SQLDICTONARY and SYSTEMSEQUENCES.

 

SQLDictonary table contains the table id for example Vendtable has 490 id.

SYSTEMSEQUENCES table contains the sequence number in nextVal field.

 

I used following code snippet which helps me to insert into AX through TSQL.

declare @tableId as int = (select tableid from SQLDICTIONARY where name like 'VendGroup' and FIELDID=0);
print @tableId;
declare @recId as bigint =(select nextval from SYSTEMSEQUENCES where TABID=@tableId and name='seqno');
print @recId;
select @recId set @recId =@recId + 1;
print @recId;
insert into DBO.VENDGROUP ([VENDGROUP],[NAME],[RECID],DATAAREAID,CREATEDBY,DEL_CREATEDTIME,CLEARINGPERIOD,PAYMTERMID,RECVERSION) values ('80','TSQLTest',@recId,'usmf','ALICIA',19372,'Net10','Net10',0 );
update SYSTEMSEQUENCES set NEXTVAL = @recId where TABID=@tableId and name='seqno';

ssss
reference:

{ 0 comments… add one }

Leave a Comment