Uploading a table

[ole90]

Hey guys,

I want to upload a very large table to my database. It has about 1,000 rows of data... how might i go about doing this?

I've tried going to phpmyadmin and pasting the whole code into the SQL box but it keeps giving me fatal error: max execution of 300 seconds blah blah

Any other methods to this?

[brazenskies]

is it an sql file you have? csv?

1000 rows is a relatively tiny amount if data

[derzok]

If you're entering it into phpmyadmin I'd assume the data is from a phpmyadmin dump, correct? In that case, look at the way the query is formatted, it's usually something like:


--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
`nick` varchar(30) NOT NULL,
`uid` varchar(32) NOT NULL,
`main` varchar(5) NOT NULL,
`date` int(11) NOT NULL,
`language` varchar(2) NOT NULL default 'en',
UNIQUE KEY `nick` (`nick`),
KEY `uid` (`uid`),
KEY `main` (`main`),
KEY `language` (`language`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`nick`, `uid`, `main`, `date`, `language`) VALUES
('abc', '44cd98d470d2bd29d7decf2237b5856b', 'true', 1199845354, 'es'),
('def', 'e9d798080f715cb42a9501f4d134ab26', 'true', 1199845361, 'en'),
// lots of rows omitted to save space
('tuv', '260ce5f40e975e53e65eb66c77e548fc', 'true', 1199845467, 'en'),
('wxy', '4f5d41f33a13f8728dcbe8b4c50a601d', 'true', 1199845522, 'en'),
('z', '4b97d1423f353e7ab361b5f0f11399b6', 'true', 1199845618, 'en');


Notice how the first part of the command is to create the table if it doesn't exist. The second part is to fill in the data. Because of the way the INSERT query works, you can easily cut the data into pieces. Just find the row you want to stop on, change the ',' to a ';' and add the same "INSERT INTO..." to the beginning of the remaining data. If it's bailing out on you, you'll have a better chance of inserting everything if you do it in chunks like that.

However I fully agree with what ole90 said - 1000 rows should take so little time. In fact, I'm willing to bet that it's timing out for other reasons than too much data being inserted at once...

[brazenskies]

If its an sql file, then place it in the bin directory if MySql, then open mysql command line client, select your dataebase and type: source file.txt

If you don't want to do that you can open your php.ini file and increase the script timeout limit

[guelphdad]

also if you don't have row inserts but raw csv data then use LOAD DATA INFILE it will be the fastest way to import your data.