From: Greg Kelley
Date: March 17 1999 6:26pm
Subject: INSERT ignoring NULLs
List-Archive: http://lists.mysql.com/mysql/442
Message-Id: <24CDBB7635B69226142B0342@usnhdata.unh.edu>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

I have created a SQL script file that creates a table then populates it with
INSERT INTO table VALUES ('blah','blah','etc')

MySQL is running under Redhat Linux v5.2

It appears that values of NULL cause subsequent field values to shift up and
fill in what should have been the NULL value.  The script seems OK - is
there a problem with MySQL and scripts?

The 5th field (expdt) instead of a getting NULL value is getting the 6th
value (28540) that belongs to field 6 (anlrt).  I have 11 tables and over
150,000 records to move from M$-Acce$$ to MySQL.

Sorry for the large mail message, but here is part of the script: (thanks
for any insights...)


CREATE TABLE HR1
     (
     usid                 CHAR(9) NOT NULL,
     psnum                CHAR(6),
     pstyp                CHAR(1),
     birdt                DATETIME,
     expdt                DATETIME,
     anlrt                REAL,
     hrlrt                REAL,
     dayyr                INT,
     hrsyr                INT,
     ftebs                CHAR(1),
     fte                  INT,
     vpr                  CHAR(2),
     band                 CHAR(1),
     amsrg                CHAR(2),
     exe                  INT,
     clscd                CHAR(5),
     bench                CHAR(1),
     hegis                CHAR(6),
     agg                  CHAR(6),
     shtxt                CHAR(15),
     octyp                CHAR(1),
     cltit                CHAR(50),
     entry                CHAR(1),
     arfrdt               DATETIME,
     clsyrs               REAL,
     aclsyrs              REAL,
     hire                 DATETIME,
     tendt                DATETIME,
     fname                CHAR(15),
     mname                CHAR(15),
     lname                CHAR(20),
     rarea                CHAR(3),
     rorg                 CHAR(4),
     objno                CHAR(4),
     arave                REAL,
     hrave                REAL,
     ams_band_arave       REAL,
     bench_arave          REAL,
     armpt                INT,
     hrmpt                REAL,
     ardef                INT,
     hrdef                REAL,
     sal_def              INT,
     bench_sal_def        INT,
     cls_sal_def          INT,
     hmin                 REAL,
     hmean                REAL,
     hmax                 REAL,
     hquart               REAL,
     amin                 INT,
     amean                INT,
     amax                 INT,
     aquart               INT,
     band_amean           INT,
     adj_amin             INT,
     adj_amean            INT,
     adj_amax             INT,
     adj_aquart           INT,
     tot_in_class         INT,
     min_hrlrt            REAL,
     avg_hrlrt            REAL,
     max_hrlrt            REAL,
     min_anlrt            INT,
     avg_anlrt            INT,
     max_anlrt            INT,
     bench_avg_anlrt      INT,
     fte_anlrt            REAL,
     aye_anlrt            INT,
     mark_pct             INT,
     emp_mark_pct         INT,
     bench_mark_pct       INT,
     over_min             INT,
     emp_sur_adj_anl      INT,
     sur_adj_anl          INT,
     emp_sur_adj_hrl      REAL,
     sur_adj_hrl          REAL,
     rpene                INT,
     pscd                 INT,
     h3quart              REAL,
     a3quart              INT,
     adj_a3quart          INT,
     KEY (usid)
     )\g

INSERT INTO HR1
VALUES (
     '012345678',
     'KAE067',
     'B',
     '1988-07-01 00:00:00',
     NULL,
     28540,
     0,
     196,
     0,
     '6',
     75,
     '15',
     NULL,
     NULL,
     1,
     '00265',
     '0',
     NULL,
     NULL,
     NULL,
     'P',
     'HEALTH EDUCATOR/COUNSELOR',
     'N',
     '1988-10-12 00:00:00',
     8.7,
     5.7,
     '1988-10-12 00:00:00',
     NULL,
     'JOHN',
     NULL,
     'SMITH',
     'KSH',
     'KC24',
     '0650',
     38797,
     0,
     0,
     0,
     37268,
     0,
     -19,
     0,
     -19,
     0,
     70,
     0,
     0,
     0,
     0,
     28920,
     36570,
     44210,
     32740,
     0,
     21690,
     27430,
     33160,
     24560,
     7,
     0,
     0,
     0,
     30290,
     36070,
     44200,
     0,
     38050,
     0,
     76,
     20,
     0,
     316,
     560,
     19474,
     0,
     0,
     597,
     2,
     NULL,
     40390,
     30290 )\g

___________________________
Greg Kelley
Information Technology Specialist
University System of NH
27 Concord Rd.
Durham, NH  03824
603.862.0959
http://usnhdata.unh.edu
http://usnh.unh.edu/analysis/