Updating data in multple companies using TSQL

Updating data in many companies can be very time consuming. To do this I often use TSQL code which loops the company table and does what I want to do in each company.

Please observe that what you do with this is totally on you own risk. Updating through SQL does not trigger validation code and can be dangerous.

Below is a template I often use as a starting point. The template is very simple, it just updates the description on Job Queue Entries, but it can be much more complex than that. Dynamic SQL is tricky to write since there’s no support from the editor. In more complex scenarios I often start in one single company with conventional TSQL to verify that the code is correct and then I convert it to dynamic SQL. The last check I do is to use PRINT(@sql) instead of EXEC(@sql) to verify that the code produced is correct.

DECLARE @sql nvarchar(MAX), @CompanyName varchar(30)
	
DECLARE CompanyCursor CURSOR FORWARD_ONLY  FOR
                      SELECT REPLACE([Name],'.','_')
                      FROM [Company]
OPEN CompanyCursor
FETCH NEXT FROM CompanyCursor INTO @CompanyName
		
WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @sql = '
    UPDATE['+@CompanyName+'$Job Queue Entry] 
    SET [Description] = ''Order Import'' 
    WHERE [Object ID to Run] = ''50000'''	
    
    EXEC (@sql)			
    FETCH NEXT FROM CompanyCursor INTO @CompanyName
END

CLOSE CompanyCursor
DEALLOCATE CompanyCursor

You can of course do this within NAV also as long as all your data is within the same database. The nice thing about using SQL is that you can reach out to other databases to get data into the NAV database. That’s often useful when doing post upgrade updates. You can use the same technique to extract data from several companies if you want to merge data into one single table.

Tags:

No comments yet.

Leave a Reply