Microsoft Dynamics AX (Archived)

Updating table from another table using Cursor

(0) Share

Report

Posted on by On-Hand Inventory I...

120

Hi,

I am using cursor to update one table from another table and this my script :

Declare @Recid bigint

Declare @ProdName nvarchar(100)

Declare @ProdComm nvarchar(100)

Declare @ProdConcat nvarchar(100)

Declare ProductName CURSOR FOR

SELECT REPLACE(SUBSTRING(DISPLAYPRODUCTNUMBER,8,20),':','-')

,B.RECID ,[NAME]

FROM [AKSAX].[dbo].[ECORESPRODUCT]AS B,[AKSAX].[dbo].[ECORESPRODUCTTRANSLATION] AS A

WHERE B.RECID=A.RECID

AND CONVERT(INT,SUBSTRING(DISPLAYPRODUCTNUMBER,1,1)) > 1

Open ProductName

Fetch Next From ProductName into @ProdComm,@Recid ,@ProdName

While (@@FETCH_STATUS =0)

Begin

SET @ProdConcat = @ProdName + ' ' + @ProdComm

BEGIN

UPDATE [AKSAX].[dbo].ECORESPRODUCTTRANSLATION SET DESCRIPTION = @ProdConcat

WHERE RECID = @Recid

END

Fetch Next From ProductName into @ProdComm,@Recid ,@ProdName

End

PRINT 'This Update is Finished'

close ProductName

DEALLOCATE ProductName

But I am not get all the records updated probably .

Best regards

Jamil

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (1)

Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

You are not supposed to do updates to AX database from external sources, since that will cause a whole lot of problems, like not invalidating records in the cache due to record version not being maintained after an update.

If you need T-SQL help, it is better to go to some forums dedicated for SQL Server, try StackOverflow or similar.

Why aren't you using tools designed for doing updates inside AX to avoid such problems, like Data Import Export Framework. It can access external databases to pull data from via ODBC just fine.

Was this reply helpful? Yes No
On-Hand Inventory I... 120 on at

Like (0)

Report

Hi Vilmos Kintera's,

I am not updating AX database from external sources,

I am try to update this ECORESPRODUCTTRANSLATION table from ECORESPRODUCT table, from same AX tables and the field which I am trying to update is description field for the item name which is empty and I am not updating the key field which is create by the AX and this description have been used in most of the AX 2012 reports, so how can I update 50000 records ,but not manually and can you give an example how to do it, because I am new to AX 2012 Development

Best regards

Jamil

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi,

You can do this updation using Excel import, have you tried anything for this?

Was this reply helpful? Yes No
AX 2012 r3 2,426 on at

Like (0)

Report

Hi Jamil,

Please make a try in your job.

First take backup

Then Try

insert_recordset ECORESPRODUCTTRANSLATION(description)

select Description from ECORESPRODUCT ;

Regards.

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report
By external source I also meant doing the update directly in SQL Server with your Transact-SQL script, which is a horrible idea and a big no-no for the above reasons.

You can update the values using multiple methods inside AX.

With an X++ job using update_recordset to only do a single trip to the database

With Data Import Export Framework

With an Excel upload (maybe?)

Was this reply helpful? Yes No
On-Hand Inventory I... 120 on at

Like (0)

Report

Good Morning Suresh,

No I did not try the Excel import ,can you tell me ,how can I export the data from the tow tables , because I was Selecting the data from the tow tables as the following :

SELECT REPLACE(SUBSTRING(B.DISPLAYPRODUCTNUMBER,8,20),':','-')

,B.RECID ,[A.NAME]

FROM [AKSAX].[dbo].[ECORESPRODUCT]AS B,[AKSAX].[dbo].[ECORESPRODUCTTRANSLATION] AS A

WHERE B.RECID=A.RECID

For example this example from the actual data in the tow tables :

This Data From ECORESPRODUCT Table

DISPLAYPRODUCTNUMBER RECID

23022 : 100 : 43 : 11/7 : 5637307150

23022 : 145 : 43 : 11/7 : 5637307149

34001 : 150 : 45 : 12/12 : 5637307148

33001 : 10 : 45 : 12/12 : 5637307147

33001 : 10 : 65 : 40 : 5637307146

23026 : 102 : 45 : 15 : 5637307145

23026 : 162 : 45 : 15 : 5637307144

23026 : 66 : 45 : 15 : 5637307143

This Data From ECORESPRODUCTTRANSLATION Table

Name RECID

SSx 5637307150

SSx 5637307149

M8z 5637307148

M8x 5637307147

M8x 5637307146

SF1x 5637307145

SF1x 5637307144

SF1x 5637307143

This is the result that to be update to the description filed

SSx 100*43*11/7

SSx 145*43*11/7

M8Z 150*45*12/12

M8X 10*45*12/12

M8X 10*65*40

SF1x 102*45*15

SF1x 162*45*15

SF1x 66*45*15

Best Regards

Jamil

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

Using Control + T combination on a grid works in most of the cases to push the data into Excel.

But the fastest approach for such a simple task is still via an X++ Job.

Was this reply helpful? Yes No
Verified answer

Vilmos Kintera 46,149 on at

Like (0)

Report
How about something like this? Validate it in a Test environment first, I'd suggest debugging and checking if the Description gives the desired results.

static void updateProductDescription(Args _args) { EcoResProduct product; EcoResProductTranslation translation; ttsBegin; while select forUpdate translation join RecId, DisplayProductNumber from product where product.RecId == translation.Product { translation.Description = strFmt('%1 %2', translation.Name, strReplace(subStr(product.DisplayProductNumber, 8, 20), ':', '-')); translation.doUpdate(); } ttsCommit; }

Was this reply helpful? Yes No
On-Hand Inventory I... 120 on at

Like (0)

Report

Hi Vilmos Kintera's,

I have committed the Update line and the commit line in your script to display the result so it display only the name that is from EcoResProductTranslation,but it is not display DisplayProductNumber

For example this example from the actual data in the tow tables :

This Data From ECORESPRODUCT Table

DISPLAYPRODUCTNUMBER RECID

23022 : 100 : 43 : 11/7 : 5637307150

23022 : 145 : 43 : 11/7 : 5637307149

34001 : 150 : 45 : 12/12 : 5637307148

33001 : 10 : 45 : 12/12 : 5637307147

This Data From ECORESPRODUCTTRANSLATION Table

Name RECID

SSx 5637307150

SSx 5637307149

M8z 5637307148

M8x 5637307147

This is the result that to be update to the description filed

SSx 100*43*11/7

SSx 145*43*11/7

M8Z 150*45*12/12

M8X 10*45*12/12

static void updateProductDescription(Args _args)

{

EcoResProduct product;

EcoResProductTranslation translation;

ttsBegin;

while select forUpdate translation

join RecId, DisplayProductNumber from product

where product.RecId == translation.Product

{

translation.Description = strFmt('%1 %2',

translation.Name,

strReplace(subStr(product.DisplayProductNumber, 8, 20), ':', '-'));

print translation.Description;

pause;

// translation.doUpdate();

}

// ttsCommit;

}

Can you help me how to get the data as the above example

Best regards

Jamil

Was this reply helpful? Yes No