In older versions of NAV I got used to using a report to move data from one table to another.  Since the last update from Microsoft for NAV 2013 R2 I am using the SQL Management Studio more and more.

For example, yesterday, I needed to change a table layout that included a change in the primary key.

I had modified the table in the developement database but was unable to import the new table because of the existing data.

I solved this by creating a temporary table with the original layout.  I can choose any number for the table that I can use it with my developement license since I will not be using the table in NAV.  I designed the original table and used File-Save As to create the new table.

Then I moved over to the SQL Management Studio.  There I found the original table and scripted a SELECT statement to a new query window.  Next I found the newly created shadow table and scripted an INSERT statement to the clipboard.  Then I combined these two statements to look like this.

[code lang=”sql”]USE [NAV_DATABASE]
GO

INSERT INTO [dbo].[NAV Company$Temp Partner Inbound Mapping]
([IC Partner Code]
,[Responsibility Center]
,[Ship-to Name]
,[Ship-to Address]
,[Ship-to Address 2]
,[Ship-to City]
,[Inbound Mapping Group Code])
SELECT
[IC Partner Code]
,[Responsibility Center]
,[Ship-to Name]
,[Ship-to Address]
,[Ship-to Address 2]
,[Ship-to City]
,[Inbound Mapping Group Code]
FROM [dbo].[NAV Company$IC Partner Inbound Mapping]
GO
DELETE FROM [dbo].[NAV Company$IC Partner Inbound Mapping]
[/code]

At this stage I have deleted all data from the table and was able to import the new modified table into the production NAV database. In this case I have two new fields in the new table. The primary changed to include a line number. That meant that I needed to create a line number for each row. This can be done in with SQL.

[code lang=”sql”]USE [NAV_DATABASE]
GO

INSERT INTO [dbo].[NAV Company$IC Partner Inbound Mapping]
([IC Partner Code]
,[Line No_]
,[Responsibility Center]
,[Ship-to Code]
,[Ship-to Name]
,[Ship-to Address]
,[Ship-to Address 2]
,[Ship-to City]
,[Inbound Mapping Group Code])
SELECT
[IC Partner Code]
,10000 * ROW_NUMBER() OVER(ORDER BY [IC Partner Code]) AS Row
,[Responsibility Center]
,”
,[Ship-to Name]
,[Ship-to Address]
,[Ship-to Address 2]
,[Ship-to City]
,[Inbound Mapping Group Code]
FROM [dbo].[NAV Company$Temp Partner Inbound Mapping]
GO

DELETE FROM [dbo].[NAV Company$Temp Partner Inbound Mapping][/code]

If I would have needed to start the row number with 10000 for every instance of IC Partner Code I would have needed to use a cursor and iterate through every instance of IC Partner Code from another table and execute the statement filtered by the IC Partner Code.  This I used for example in the Azure Backup Script.

I finished this process by deleting the temporary table for the production database.

 

Leave a Reply

%d bloggers like this: