Geek Answers Handbook

How to skip an INSERT column record when a value is empty in SQL?

I insert many rows into the database, and some of the columns are empty for some rows .

How can I insert without assigning a dummy value to these empty 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 Leads VALUES('name', 'cityName', 9, 'anotherValue')

My problem is line 2 where there is an empty value between the city name and another value. Ideally, I want the value to remain zero.

Any help is appreciated.

Thanks!

+4
source share
6 answers

Just tell him to insert zero:

  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')
+8
source

You should always explicitly indicate which columns you are inserting - then you can leave the ones you don't want:

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

(excluding Col3 here in this example)

+11
source

You will need to find these fields and insert NULL. The regular expression /, \ s *, / should identify spaces. If you are using linux / mac try:

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

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 will also work for columns with default values ​​and / or columns that you cannot insert a value (e.g. TIMESTAMP)

+1
source

You need to use null if there is no value.

0
source

I had a problem with an existing application running in prod, but not with a new local installation.

Mysql should have been configured as follows:

 sql-mode=MYSQL40

and then it will take the line as:

  INSERT Leads VALUES('name', 'cityName', , 'anotherValue')
-1
source

More articles:


All Articles