SQL INSERT - DATABASE CREATION

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

SQL INSERT - DATABASE CREATION

Author

Topic

krodfish
Starting Member

3 Posts

Posted - 2012-02-04 : 20:11:44

I'm new to SQL - I created a database called "movies" and then created the table "directors". I was successful on creating the table and the columns, but somehow when I use my insert command it either giver me an error or it don't do anything...I can't find anything wrong on the insert statement...can anyone see anything wrong with it? Thanks in advance!

THE TABLE WITH THE COLUMNS WERE SUCCESSFULLY CREATED....

CREATE TABLE DIRECTORS
(
dir_id char(4) not null primary key,
dir_lname varchar(40) not null,
dir_fname varchar(20) not null,
dir_phone char(12) null,
dir_address varchar(20) null,
dir_city varchar(20) null,
dir_state char(2) null,
dir_zip char(5) null
);

//THIS STATEMENT FAILS ALL THE TIME OR JUST DOESN"T RUN ( PLEASE HELP)

INSERT INTO DIRECTORS (dir_id, dir_lname, dir_fname, dir_phone, dir_address, dir_City, dir_state, dir_zip)
VALUES (D001 , 'Lawrence", 'Martin', 415658-9932, '6223 Bateman', 'Berkley', 'CA', 94705);
INSERT INTO DIRECTORS (dir_id, dir_lname, dir_fname, dir_phone, dir_address, dir_City, dir_state, dir_zip)
VALUES (D002,'Leker','Larry','415986-7020','309 63rd St #441','Oakland','CA', 94618);
INSERT INTO DIRECTORS (dir_id, dir_lname, dir_fname, dir_phone, dir_address, dir_City, dir_state, dir_zip)
VALUES (D003,'Nichols','Mike','415548-7723','589 Darwin Ln.','Berkley','CA', 94705);
INSERT INTO DIRECTORS (dir_id, dir_lname, dir_fname, dir_phone, dir_address, dir_City, dir_state, dir_zip)
VALUES (D004,'Schaeffer','Eric','801826-0752','67 Seventh Avenue','Salt Lake City','UT',84152);
INSERT INTO DIRECTORS (dir_id, dir_lname, dir_fname, dir_phone, dir_address, dir_City, dir_state, dir_zip)
VALUES (D005,'Frears','Stephen','801826-0742','3 Balding Pl','Salt Lake City','UT', 84152);

krodfish
Starting Member

3 Posts

Posted - 2012-02-04 : 20:17:51

THE ERROR I'M GETTING IS THIS...

mysql> INSERT INTO DIRECTORS (dir_id, dir_lname, dir_fname, dir_phone, dir_address, dir_City, dir_state, dir_zip)
-> VALUES (D001 , 'Lawrence", 'Martin', 415658-9932, '6223 Bateman', 'Berkley', 'CA', 94705);
'> INSERT INTO DIRECTORS (dir_id, dir_lname, dir_fname, dir_phone, dir_address, dir_City, dir_state, dir_zip)
'> VALUES (D002,'Leker','Larry','415986-7020','309 63rd St #441','Oakland','CA', 94618);
-> INSERT INTO DIRECTORS (dir_id, dir_lname, dir_fname, dir_phone, dir_address, dir_City, dir_state, dir_zip)
-> VALUES (D003,'Nichols','Mike','415548-7723','589 Darwin Ln.','Berkley','CA', 94705);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Martin', 415658-9932, '62
23 Bateman', 'Berkley', 'CA', 94705);
INSERT INTO DIREC' at line 2
mysql> INSERT INTO DIRECTORS (dir_id, dir_lname, dir_fname, dir_phone, dir_address, dir_City, dir_state, dir_zip)
-> VALUES (D004,'Schaeffer','Eric','801826-0752','67 Seventh Avenue','Salt Lake City','UT',84152);
ERROR 1054 (42S22): Unknown column 'D004' in 'field list'
mysql> INSERT INTO DIRECTORS (dir_id, dir_lname, dir_fname, dir_phone, dir_address, dir_City, dir_state, dir_zip)
-> VALUES (D005,'Frears','Stephen','801826-0742','3 Balding Pl','Salt Lake City','UT', 84152);

Kristen
Test

22859 Posts

Posted - 2012-02-05 : 03:18:59

All your string constants need single-quotes around them:


INSERT INTO DIRECTORS (dir_id, dir_lname, dir_fname, dir_phone, dir_address, dir_City, dir_state, dir_zip)
VALUES ('D001' , 'Lawrence', 'Martin', '415658-9932', '6223 Bateman', 'Berkley', 'CA', '94705');

and I've changed the double quote (") you had after Lawrence

If you have numbers then you don't need the quote, and in some instances SQL will make an implicit conversion

Please note that this is a Microsoft SQL forum, so folk here may not know much/anything about MySQL (although this question is generic to all flavours fo SQL, so I'm reasonably confident I can help you, even though I've never used MySQL

)

krodfish
Starting Member

3 Posts

Posted - 2012-02-05 : 10:54:26

OMG! That Lawrence double quote was holding me back...I must been exhausted and didn't catch it. Thank you very much!

Thanks for the heads up that I'm in the incorrect forum, you were able to help me though. Thanks again!

Kristen
Test

22859 Posts

Posted - 2012-02-05 : 14:30:53

"That Lawrence double quote was holding me back."

Hahaha ... Been there ... Done that .... Got the T-Shirt !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources