How do I skip a column entry on INSERT when a value is blank in SQL?

I'm inserting lots of rows into a database and some of the columns are blank for some of the rows.

How can I insert without assigning a dummy value to these blank fields?

       1 INSERT Leads VALUES('name', 'cityName', 5, 'anotherValue')
       2 INSERT Leads VALUES('name', 'cityName',  , 'anotherValue')
       3 INSERT Leads VALUES('name', 'cityName', 2, 'anotherValue')
       4 INSERT开发者_JAVA百科 Leads VALUES('name', 'cityName', 9, 'anotherValue')

My problem lies in row 2 where there is a blank value in between city name and another value. Ideally, I'd like the value to remain null.

Any help is appreciated.

Thanks!

You should always explicitly specify which columns you're inserting to - then you can leave out those you don't want:

INSERT INTO dbo.Leads(Col1, Col2, Col4) VALUES('name', 'cityName', 'anotherValue')

(leaving out Col3 here in this example)

Just tell it to insert a null:

    INSERT Leads VALUES('name', 'cityName', 5, 'anotherValue')
    INSERT Leads VALUES('name', 'cityName', null , 'anotherValue')
    INSERT Leads VALUES('name', 'cityName', 2, 'anotherValue')
    INSERT Leads VALUES('name', 'cityName', 9, 'anotherValue')

You will have to find these fields and insert a NULL. The regular expression /,\s*,/ should identify the gaps. If you're running a linux/mac try:

perl -pi -e 's/,\s*,/, NULL,/g' path/to/file.sql

Another method you can use:

INSERT Leads VALUES('name', 'cityName', 5, 'anotherValue')
INSERT Leads VALUES('name', 'cityName', DEFAULT, 'anotherValue')
INSERT Leads VALUES('name', 'cityName', 2, 'anotherValue')
INSERT Leads VALUES('name', 'cityName', 9, 'anotherValue')

This would also work for columns that have default values and\or columns that you can't insert a value to (like TIMESTAMP)

You need to use null if there is no value.

For Insert statements Use Values(Null,...) if field accepts Null

Use Values('',...) if field has (,not null) requirement,and for fields where you do not want Null to display e.g. a Note field.

I had the problem with an existing application that worked in prod but not on a fresh local install.

Mysql had to be configured like this :

sql-mode=MYSQL40

and then it will accept row like :

 INSERT Leads VALUES('name', 'cityName',  , 'anotherValue')