1

I have a field called Super_ssn which is of type BIGINT. I am trying to load the data from a text file and the data is loading into all the fields properly, except if the value if NULL. This is the query I used to populate the data into the table.

LOAD DATA LOCAL INFILE '<PATH-TO-FILE>' INTO TABLE EMPLOYEE FIELDS TERMINATED BY ', ' ENCLOSED BY "'" LINES TERMINATED BY '\n';

I don't want to post the entire txt file data here, since it is reasonably big. I am just posting the first 4 records.

'James', 'E', 'Borg', '888665555', '10-NOV-1927', '450 Stone,Houston,TX', 'M', 55000, null, 1
'Franklin', 'T', 'Wong', '333445555', '08-DEC-1945', '638 Voss,Houston,TX', 'M', 40000, '888665555', 5
'Jennifer', 'S', 'Wallace', '987654321', '20-JUN-1931', '291 Berry,Bellaire,TX', 'F', 43000, '888665555', 4
'Jared', 'D', 'James', '111111100', '10-OCT-1966', '123 Peachtr,Atlanta,GA', 'M', 85000, null, 6

And the description of the table columns result, please find below.

+-----------+-----------------------------+------+-----+---------+-------+
| Field     | Type                        | Null | Key | Default | Extra |
+-----------+-----------------------------+------+-----+---------+-------+
| Fname     | varchar(40)                 | NO   |     | NULL    |       |
| Minit     | char(2)                     | YES  |     | NULL    |       |
| Lname     | varchar(40)                 | NO   |     | NULL    |       |
| SSN       | bigint(9) unsigned zerofill | NO   | PRI | NULL    |       |
| Bdate     | varchar(30)                 | YES  |     | NULL    |       |
| Address   | varchar(100)                | YES  |     | NULL    |       |
| Sex       | char(2)                     | YES  |     | NULL    |       |
| Salary    | int(11)                     | YES  |     | NULL    |       |
| Super_ssn | bigint(20)                  | YES  |     | NULL    |       |
| Dno       | tinyint(4)                  | YES  |     | NULL    |       |
+-----------+-----------------------------+------+-----+---------+-------+

Since the Super_ssn is a BIGINT and there is null in the data, the value that is stored once the data is loaded is 0.

What I think is happening is, I know when we use ENCLOSED BY in the load data query, the null will be stored as 'NULL' in the database. Since because, Super_ssn is a BIGINT and there is a String, it is populating as 0. Am I correct?

If I am correct, how can I still insert NULL in the database, preserving the data type of Super_ssn as BIGINT.

peterh
  • 11,875
  • 18
  • 85
  • 108
Ramji Seetharaman
  • 524
  • 4
  • 7
  • 24
  • Welcome to SO! Please review the following link on writing a good question and edit accordingly: https://stackoverflow.com/help/how-to-ask – Sneha Jul 18 '17 at 12:03
  • I don't think there is nothing wrong with my question! Just the formatting isn't proper. – Ramji Seetharaman Jul 18 '17 at 12:05
  • Its better to not TYPE IN ALL CAPS; this is read as shouting and considered rude. – Sneha Jul 18 '17 at 12:40
  • 1
    This all capital, long bolded page things look very bad here. You don't attract more attention with it, you attract only downvotes and close votes. Your apology don't matter, the solution isn't that writing complete paragraphs in cpiatal case and then apologize for it, the solution is that you write interesting questions - in lowercase, without bold. – peterh Jul 18 '17 at 12:46
  • But don't worry, you will learn that easily. I fixed your format. [Here](https://stackoverflow.com/posts/45164231/revisions) you can see, what exactly I've changed. – peterh Jul 18 '17 at 12:50
  • @peterh - Thanks a lot for the formatting! – Ramji Seetharaman Jul 18 '17 at 14:24

0 Answers0