Importing data from ODBC database into Dynamics NAV is a regular task. The most common problem is where the external database has data types and null values that are not supported by Dynamics NAV.
I have solved this with a function that builds the query command. The function selects all fields from the table and then loops through them, checks the data type and builds an new query command using both “ISNULL” and “CONVERT” functions.
The attached example is where I import customers and vendors from Pervasive database with DK business data. The create function requires the table name and the primary key. If the external table has to many fields to fit into one query command more queries will be created for remaining fields along with the primary key.
Here you can download a ZIP file with the example codeunit.