inserting records into a database

[yechi2001]

I am using mysql 4.1 and I am trying to insert a sql file into a database. I have the mysql> prompt but I cannot figure out the script to insert the sql file. I have tried using th database name followed by < then the file name but this doesnt work. Help me please.

 

[nego78]

mysql -u username -p -D nameofdatabase < /pathtofile/file.sql

gry online

 

[yechi2001]

I tried that but it didnt work. I keep on getting the syntax rror in line one thing. how should the file path look? Should it be from the root i.e. C: etc or from the the mysql file. Please give me the exact thing to type. For examle my database is called globalcar and the file is called insert so should I type globalcar < /c:/program files/mysql/mysql server 4.1/bin/insert.sql or what. Thanks in advance.

 

[TonyGroves]

If you're getting a syntax error message, then the fault is in your SQL file. If you can't locate the fault, then you could post the text of your file (or the most significant part) here.

 

[nego78]

yechi2001:

c:/program files/mysql/mysql server 4.1/bin/mysql -u username -p -D nameofdatabase < /pathtofile/file.sql

Give me exactly name and locatyion of your sql file and i give you exact command
And as Tony mentioned that can be error in your sql

Please write here 'error number'

gry online

 

[yechi2001]

Thanks error number 1064 (42000)

this is the contents of the sql file

#
# Table structure for table `comments`
#

CREATE TABLE comments (
COMMENT_ID int(11) NOT NULL auto_increment,
FIRST_NAME varchar(50) default NULL,
LAST_NAME varchar(50) default NULL,
TELEPHONE varchar(20) default NULL,
EMAIL varchar(50) default NULL,
SUBMIT_DATE datetime default NULL,
COMMENTS longtext,
ANSWERED tinyint(4) default NULL,
PRIMARY KEY (COMMENT_ID),
KEY PRIMARY_KEY (COMMENT_ID)
) TYPE=MyISAM;

#
# Dumping data for table `comments`
#

INSERT INTO comments (COMMENT_ID, FIRST_NAME, LAST_NAME, TELEPHONE, EMAIL, SUBMIT_DATE, COMMENTS, ANSWERED) VALUES (1, 'Letitia', 'Riley', '210-651-0964', 'letitiariley@juno.com', '2002-02-13 00:00:00', 'Are there any plans to put a facility in Anchorage Alaska?', 1);
INSERT INTO comments (COMMENT_ID, FIRST_NAME, LAST_NAME, TELEPHONE, EMAIL, SUBMIT_DATE, COMMENTS, ANSWERED) VALUES (2, 'Sachiko', 'Matsuda', '(0) 476 322 802', 'sachi@web.net', '2002-03-20 00:00:00', 'Unbelievably excellent service received at your New Tokyo airport facility.', 0);
INSERT INTO comments (COMMENT_ID, FIRST_NAME, LAST_NAME, TELEPHONE, EMAIL, SUBMIT_DATE, COMMENTS, ANSWERED) VALUES (3, 'Dieter', 'Dietrich', '', 'dd@worldly.com', '2002-03-24 00:00:00', 'Does your Heathrow office lease automobiles?', 0);
# --------------------------------------------------------

#
# Table structure for table `locations`
#

CREATE TABLE locations (
CODE char(4) NOT NULL default '',
LOCATION_NAME char(100) default NULL,
ADDRESS char(50) default NULL,
CITY char(50) default NULL,
STATE_COUNTRY char(50) default NULL,
REGION_ID int(11) NOT NULL default '0',
TELEPHONE char(20) default NULL,
FAX char(20) default NULL,
PRIMARY KEY (CODE),
KEY code (CODE),
KEY REGION_ID (REGION_ID)
) TYPE=MyISAM;

#
# Dumping data for table `locations`
#

INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('BWI', 'Baltimore-Washington International', 'Airport Blvd', 'Baltimore', 'MD', 1, '410-121-1222', '410-121-1223');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('CAI', 'Cairo International Airport', 'Cairo International Airport', 'Cairo', 'Egypt', 4, '2022652432', '2022652433');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('CBR', 'Canberra', 'Canberra Airport', 'Canberra', 'Australia', 6, '02 62496211', '02 62496212');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('CNS', 'Cairns', 'Cairns Airport', 'Cairns', 'Queensland', 6, '07 40359300', '07 40359300');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('CPT', 'Cape Town Airport', 'Cape Town Airport', 'Cape Town', 'South Africa', 4, '0272 19 343914', '0272 19 343915');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('CWB', 'Afonso Pena', 'Av. N. Sra Da Aparecida 904', 'Curitiba', 'Brazil', 2, '55 412698 010', '55 412698 011');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('CZM', 'Aeropuerto Intl De Cozumel', 'Cozumel Airport', 'Cozumel', 'Mexico', 2, '5298723888', '5298723889');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('DEN', 'Denver International', '100 Pena Blvd', 'Denver', 'CO', 1, '303-667-6555', '303-667-6556');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('DFW', 'Dallas Ft Worth International', '3838 North Bound Service Road', 'Dallas/Ft Worth', 'TX', 1, '972-455-3333', '972-455-3334');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('EZE', 'Eze', 'Autopista Tte Ricchierei', 'Buenos Aires', 'Argentina', 2, '00-54-4480-0055', '00-54-4480-0056');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('FRA', 'Frankfurt Airport', 'General Aviation Terminal - Frankfurt Airport', 'Frankfurt', 'Germany', 3, '069 69593245', '069 69593246');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('KIX', 'Kansai International Airport', 'Senshu-kuko Kita', 'Osaka', 'Japan', 5, '(0)724 552 500', '(0)724 552 500');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('LHR', 'Heathrow Airport', 'Heathrow Airport', 'London', 'United Kingdom', 3, '020 88972075', '020 88972074');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('MCI', 'Kansas City International Airport', '902 Tel Aviv Ave', 'Kansas City', 'MO', 1, '816-243-5765', '816-243-5591');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('MSP', 'Minneapolis/St Paul Intl Airport', '4300 Glumack Drive', 'Minneapolis', 'MN', 1, '651-698-9585', '651-698-3062');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('MUC', 'Franz-Josef-Strauss Airport', 'Franz-Josef-Strauss Airport', 'Munich', 'Germany', 3, '089 978861', '089 978865');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('NRT', 'New Tokyo International Airport', 'Chiba 282-8601', 'Narita', 'Japan', 5, '(0)476 322 802', '(0)476 322 802');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('PMO', 'Punta Raisi', 'Punta Raisi Airport - Sicily', 'Palermo', 'Sicily', 3, '091 213 113', '091 213 113');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('PNA', 'Noain', 'Airport', 'Pamplona', 'Spain', 3, '948 311596', '948 311596');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('SIN', 'Singapore Changi Airport', 'Singapore Changi Airport', 'Singapore', 'Singapore', 5, '(65)542-5300', '(65)542-5301');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('TPS', 'Vincenzo-Florio Airport', 'Vincenzo-Florio Airport', 'Trapani', 'Italy', 3, '0923 842666', '0923 842667');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('YGK', 'Kingston Airport', '676 Princess St.', 'Kingston', 'Ontario', 1, '613-531-2145', '613-531-2146');
INSERT INTO locations (CODE, LOCATION_NAME, ADDRESS, CITY, STATE_COUNTRY, REGION_ID, TELEPHONE, FAX) VALUES ('YQG', 'Windsor International', 'Windsor Airport', 'Windsor', 'Ontario', 1, '519-250-3455', '519-250-3456');
# --------------------------------------------------------

#
# Table structure for table `region`
#

CREATE TABLE region (
ID int(11) NOT NULL auto_increment,
NAME char(50) default NULL,
PRIMARY KEY (ID)
) TYPE=MyISAM;

#
# Dumping data for table `region`
#

INSERT INTO region (ID, NAME) VALUES (1, 'North America');
INSERT INTO region (ID, NAME) VALUES (2, 'South/Central America');
INSERT INTO region (ID, NAME) VALUES (3, 'Europe');
INSERT INTO region (ID, NAME) VALUES (4, 'Africa');
INSERT INTO region (ID, NAME) VALUES (5, 'Asia');
INSERT INTO region (ID, NAME) VALUES (6, 'Australia');


the exact location of this file is c:\insert.sql.

Thanks again

 

[TonyGroves]

And what is the text of the syntax error message?

 

[namida]

I tried the whole sql you pasted in my Sql and it works, nothing wrong.
If you try to execute the sql (and the first time it was successful) it will give you an error. Check your database perhaps half of it was executed successfully?

Regards,

Namida

 

[webgeo99]

the easiest way to insert a text file into a mysql database
is very simple. Just make sure that the fields in the text
file match up with the fields in your database and the text
file is in your MySql/bin directory and do the following and
your all set.

C:\> mysql mydb (name of database to use)
mysql> LOAD DATA LOCAL INFILE 'mytable.txt' (name of text file)
-> INTO TABLE mytable (name of table to insert into)
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';

FIELDS TERMINATED BY means that when mysql sees a comma
it should move to the next field. like this...
"myfield1","myfield2" when mysql sees the comma, it moves
to "myfield2".
ENCLOSED BY means that each field is in closed by a double quote. like this "myfield"

LINES TERMINATED BY means that when mysql detects a carrige
return OR a new line it should move to the next row.
BE CAREFUL WITH THIS. if you have a row that raps around
to the next line, mysql will insert that info in a new row.
use '\r' if your not sure that all the information in the
file is on the same line. Good luck. Hope this helps.