SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
-
Aug 28, 2006, 04:11 #1
LOAD DATA INFILE - Returns empty datetime
Hello,
I have a table with about 1 million rows. I want to use LOAD DATA INFILE because it's fast. Everything works, except: the datetime (last column of the row) is empty (0000-00-00 00:00:00).
This is the query I used:
HTML Code:LOAD DATA INFILE '/usr/tmpsql/db.sql' INTO TABLE track FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
HTML Code:CREATE TABLE `track` ( `id` int(11) NOT NULL auto_increment, `nr` int(11) NOT NULL default '0', `ip` int(10) unsigned NOT NULL default '0', `ndate` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`), KEY `ip` (`ip`), KEY `ndate` (`ndate`), KEY `nr` (`nr`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
HTML Code:INSERT INTO `track` VALUES (4463027, 35225, 1154087817, '2006-08-24 05:56:57'); INSERT INTO `track` VALUES (4463026, 47519, 1083028739, '2006-08-24 05:56:57');
-
Aug 28, 2006, 04:28 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
you would not LOAD that sql file, you would execute it
-
Aug 28, 2006, 04:36 #3
Originally Posted by r937
-
Mar 30, 2009, 22:46 #4
- Join Date
- Mar 2009
- Posts
- 10
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
hi ,
i also got the problem
at first i execute the command text as follows
cmd.CommandText = "Load Data Local Infile '" + filePath + "' Into Table testtable1 (date,col1,col2) ;";
but when it load to database table ,the first row of date fields always return 0000-00-00 00:00:00
then i solved the problem with following code
cmd.CommandText = "Load Data Local Infile '" + filePath + "' Into Table testtable1 Lines STARTING BY '##Start##' (date,col1,col2) ;";
Hope it help.
Thanks
Bookmarks