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/