CODEWITHSUNDEEP
sqlsql-server
Barrett Kuethen
Barrett Kuethen

Reputation: 1914

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 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!

Upvotes: 9

Views: 43933

Answers (7)

Andrew
Andrew

Reputation: 1

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.

Upvotes: 0

jpprade
jpprade

Reputation: 3664

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')

Upvotes: -1

Mor Shemesh
Mor Shemesh

Reputation: 2899

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)

Upvotes: 2

rodneyrehm
rodneyrehm

Reputation: 13557

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

Upvotes: 2

Watermark Studios
Watermark Studios

Reputation: 1183

You need to use null if there is no value.

Upvotes: 0

Abe Miessler
Abe Miessler

Reputation: 85116

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')

Upvotes: 11

marc_s
marc_s

Reputation: 755128

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)

Upvotes: 20

Related Questions