Ali Raza Zaidi

A practitioner’s musings on Dynamics 365 Finance and Operations

TSQL

How to get next recid in TSQL Dynamics Ax 2012

by

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:

Using a Common Table Expression (CTE) with an UPDATE statement

by

For update part of ETL Process I have to write another Common table expression to get  all records where are changed at source to run update query on destination table

 

My update part of basic ETL using TSQL as

 

with ChangedRows as

(

 

SELECT     sourceEmp.empid, sourceEmp.lastname, sourceEmp.firstname, sourceEmp.title, sourceEmp.titleofcourtesy, sourceEmp.birthdate, sourceEmp.hiredate, sourceEmp.address, sourceEmp.city, sourceEmp.region, sourceEmp.postalcode, sourceEmp.country, sourceEmp.phone, sourceEmp.mgrid,DestEmployee.SourceEmpId

FROM         TSQLFundamentals2008.HR.Employees sourceEmp inner join

TSQLFundamentals2008DW.HR.DimEmployees DestEmployee

on

sourceEmp.empid = DestEmployee.SourceEmpId

where

 

sourceEmp.lastname <> DestEmployee.lastname

or sourceEmp.firstname <> DestEmployee.firstname

or sourceEmp.title <> DestEmployee.title

or sourceEmp.titleofcourtesy <>DestEmployee.titleofcourtesy

or sourceEmp.birthdate<>DestEmployee.birthdate

or sourceEmp.hiredate <> DestEmployee.hiredate

or sourceEmp.address <> DestEmployee.address

or sourceEmp.city <> DestEmployee.city

or sourceEmp.region <> DestEmployee.region

or sourceEmp.postalcode<>DestEmployee.postalcode

or sourceEmp.country <>DestEmployee.country

or sourceEmp.phone<>DestEmployee.phone

or sourceEmp.mgrid <> DestEmployee.mgrid

 

)

update  TSQLFundamentals2008DW.HR.DimEmployees

set    lastname = ChangedRows.lastname

,  firstname = ChangedRows.firstname

,  title = ChangedRows.title

,  titleofcourtesy =ChangedRows.titleofcourtesy

,  birthdate=ChangedRows.birthdate

,  hiredate = ChangedRows.hiredate

,  address = ChangedRows.address

,  city = ChangedRows.city

,  region = ChangedRows.region

,  postalcode=ChangedRows.postalcode

,  country =ChangedRows.country

,  phone=ChangedRows.phone

,  mgrid = ChangedRows.mgrid

from ChangedRows

where TSQLFundamentals2008DW.HR.DimEmployees.SourceEmpId=ChangedRows.empid