Insert Values By SQL

bbcai

Is it possible to insert blank values with SQL like Navision insert the data into table cause i saw most of the fields in Navision SQL Server is not allowed null values?

itspeter

If not mistaken, Navision never store NULL, but it store empty string.

kine

There is one property on the Database..

Look there - Open Navision, connect to the DB, main menu - File - Database -
Alter -Integration -Maintain Defaults - check it, save it... you will have
defaults created and you do not need to enter all fields if you are
inserting data...

BUT!!! Inserting data with external software into Navision can corrupt your
database if you do not know exactly all aspects and impact of your work...
(for example some connections can be corrupted etc.) I recommand you to
import data only into some special tables, created only for that purpose,
and transfer the data to standard tables with C/AL code to be able to run
all validations etc... But please, do not import Accounting data in this
way, use journals...

jonsan21

Hi,

If you know what you're doing - yes you can do that. You can let the SQL's Allow Null property for a particular field and then key in NULL values to that field.

However as itspeter mentioned, when you recompiled the Navision table (in Navision) it will reset the Allow Null property - as Navision seems to always insert empty string instead of NULL values.

Rgds,

Jon.

kine

Yes, setting something directly on SQL is no solution. Use the standard Navision property for the defaults and you have correct solution. It will only set defaults to '' or 0 on all fields (and the first date on all date fields).

Nobody

It is easy really, just not intuitive.

Just use '' (single quote, single quote) for the value in the insert statement. This will insert a "blank" value without using NULLS into the field. That is how the NDBCS.DLL in Navision does it.

Example
Insert Tablename(Field1, Field2), Values('','')

FYI - Using NULLS in TSQL design and programming can be dangerous to your health. =;

kine

Nobody wrote:

It is easy really, just not intuitive.

Just use '' (single quote, single quote) for the value in the insert statement.

Yes, but after you add some field, you must correct the TSQL... it is very hard to keep it in sync. The defaults on SQL solve it...