Microsoft Dynamics NAV (Archived)

Insert data into NAV created tables thru SQL

(0) Share

Report

Posted on by Community Member

Is it possible to insert data from another database to NAV created tables thru backend, I mean thru SQL level?

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

keoma 32,729 on at

Like (0)

Report

inserting/modifying data on sql level is in case of nav no good idea, because the according triggers on nav level are not run and the data integrity gets lost. so, better use web services to insert data. the nav source database provides a webservice (published codeunit, ...), which is consumed by nav target database.

Was this reply helpful? Yes No
Suggested answer

Tharanga Chandrasekara 23,118 on at

Like (0)

Report

Simple answer is Yes. You can insert data to NAV through direct SQL. However the concern is, is that the correct practice go forward.

When you insert data into NAV tables directly through SQL, NAV will not check any data validation or fire any trigger codes written in the table level. Therefore this is very risky and not recommended to proceed.

What you can do is create some temp tables in NAV database and then push your data those tables. Once the data transfer is completed you can create a C/AL codes for the data integration to original NAV tables. This will fire the triggers in NAV and you will have no issues.

Was this reply helpful? Yes No
Suggested answer

ksundeep87 800 on at

Like (0)

Report

Hi,

May I know what exactly you are trying to do.

Insert data from NAV Tables from other NAV instance? or Insert data from any linked Servers ?

Are those the custom tables or base tables?

If it is for any custom table from Linked Servers it is very simple, you can use Linked Objects property of the NAV tables. Check the below links. (never insert from back-end as it will bypass all the validations)

http://dynamicsuser.net/blogs/waldo/archive/2008/11/08/how-to-display-an-sql-server-view-in-microsoft-dynamics-nav.aspx

https://msdn.microsoft.com/en-us/library/dd338982.aspx

http://forum.mibuso.com/discussion/30778/linked-object-and-sql-view

Hope this will help.

Was this reply helpful? Yes No
Suggested answer

Roberto Stefanetti 12,998 on at

Like (0)

Report

hi,

IS NOT best practice (.Microsoft suggest to use Rapidstart Services, XML ports or exposed Web Services to import\export data) ... but you can use a sql "SELECT INTO STATEMENT" to do this (can be useful for fast demo scenarios etc. and you can schedule..)

look at this link:

NAV to NAV SQL Fast Data Migration (with dynamic “INSERT INTO” STATEMENT)

robertostefanettinavblog.wordpress.com/.../nav-to-nav-sql-fast-data-migration-with-dynamic-insert-into-statements

or on my MSDN Page

gallery.technet.microsoft.com/NAV-to-NAV-SQL-Fast-Data-4341b7e9

Was this reply helpful? Yes No
Alex A 2,913 on at

Like (0)

Report

Please tell us specifically what you need to do. What is the end result you are trying to achieve?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Best practice are "don't do any operation in SQL"; but you can access SQL data source by codeunit (I hav a SQL Utility CodeUnit for that purpose) using net objs, then do read data from NAV CU and insert data in your instance with NAV C/AL functions.

This approach allows non only nav triggering, but also the defautl values for every field in your NAV tabel record (NAV instance crashes with nulls).

B/R

Was this reply helpful? Yes No