Transfer Data into Microsoft Dynamics NAV Using SQL Scripts



Let’s take a look at what is involved in the SQL INSERT INTO SELECT Statement.

In The Example below I have selected the No. Series Table:


/****** Script for Transferring data using SQL INSERT INTO SELECT ******/

Insert Into [Demo Database NAV (8-0)].[dbo].[CRONUS USA, Inc_$No_ Series]

     ( [Code]

      ,[Description]

      ,[Default Nos_]

      ,[Manual Nos_]

      ,[Date Order])

SELECT

       [Code]

      ,[Description]

      ,[Default Nos_]

      ,[Manual Nos_]

      ,[Date Order]

FROM [Demo Database NAV (8-0)].[dbo].[CRONUS Canada, Inc_$No_ Series]


This SQL statement is taking the data in the CRONUS Canada, Inc. Company and transferring it to the CRONUS USA, Inc. Company.

It important to remember you must list each column you are going to transfer in both the Insert and Select Statements.

Note: You must exclude the [timestamp] column because you will get the following error:

Msg 273, Level 16, State 1, Line 1

Cannot insert an explicit value into a timestamp column.

Once you have your SQL Statement complete you will execute it using the F5 Button.

(129 row(s) affected)

The biggest drawback when using the SQL INSERT INTO SELECT Statement is that you must remember that business logic is not being executed.  This means that no data validation is being performed and any onValidate trigger code is not executed.  This has the potential for corrupting your database so the SQL INSERT INTO SELECT Statement must be used when you know that the data that you are transferring has already been processed by Dynamics NAV Business Logic Code.

The SQL INSERT INTO SELECT Statement is a powerful tool but you must weigh the Pros and Cons.

Pros

  1. High Performance

  2. Does not require NAV database Objects.

  3. Can be performed by a SQL User.

  4. Can be scheduled without the need of Job Queue.

 Cons

  1. Does not validate business logic in Dynamics NAV.

  2. Special consideration must be paid to the Timestamp field.

  3. Does not work for the classic database environment.

  4. Does not check user’s permissions table for access.

I hope the next project that requires you to transfer data that you consider using the SQL INSERT INTO SELECT Statement.

#DynamicsNAV

Microsoft Gold Partner Logo_edited.png
  • LinkedIn Social Icon
  • Twitter Social Icon
  • Facebook Social Icon
  • YouTube Social  Icon

Austin  |  Dallas  |  Houston  |  New Orleans  |  Nashville