To quote to not to quote - Insert Statements

[msdbsql]

INSERT INTO `products` (`product_id`, `product_name`, `level`, `gender`) VALUES
('1',New Balance 855,'1','men'),
('2',Brooks ASR2,'1','men'),
('3',Montrail Hurricane Ridge XCR,'1','men'),
('4',Spira Genesis,'1','men'),
('5',Spira Vortex,'1','men'),
('6',New Balance 587,'1','men'),
('7',Merrell Stormfront,'1','men'),
('8',Merrell Motovator,'1','men'),
('9',Saucony 3D Grid Hurricane 6,'1','men')

I am using a tool extract this data from a table called products to a remote server with a table with the same name.(using phpmysqladmin) For some reason in the extraction process the script created does not include all the quotes, when I place the quotes around the product name it still errors out.

#1064 - You have an error in your SQL syntax near 'Balance 855,'1','men'),
('2',Brooks ASR2,'1','men'),
('3',Montrail Hurrica' at line 2

I can't determine what's missing, especially because I am using a mysql tool to extract the data to create this script.

 

[sleipnir214]

You put singlequotes around strings. New Balance 855 is a string. Put quotes around it.

If either of the columns product_id or level are numeric types, omit the quotes around the values you are inserting.

The backticks around table and column names are only necessary if you do the unwise thing of naming a table or column using a MySQL reserved word.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[msdbsql]

These are the table properties:

CREATE TABLE `products` (
`product_id` int(11) default NULL,
`product_name` varchar(50) default NULL,
`level` int(11) default NULL,
`gender` varchar(10) default NULL
) TYPE=InnoDB;


Based on your advice since 'level' and 'product_id' are not char or varchar I should omit the quotes?

I tried putting quotes around the product_name as you suggested, but it still errored out with the error in the original post.

 

[sleipnir214]

Given that table definition, I would use an INSERT query of:

INSERT INTO products (product_id, product_name, level, gender) VALUES
(1,'New Balance 855',1,'men')

to insert the first value set.


Want the best answers? Ask the best questions!

TANSTAAFL!!