Insert values without mentioning the column name

Question

So i have tried inserting the values without mentioning the column name in the code..

After some searching, I have found that it is possible by passing the value as null as mentioned here.But when I did it, it threw some errors.

The code i have used

CREATE TABLE Persons
(
    ID int  NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    PRIMARY KEY (ID)
);

insert into Persons values (NULL,1);

So how canI add the values without mentioning the column name?

Thanx in advance

List all the columns for which you are inserting data. Do you have a problem with that? It is a best practice. — Gordon Linoff, Oct 20 '14 at 12:42
@GordonLinoff i just want to know how cant it be inserted without mentioning ..can you provide me an sql fiddle showing the example — frgdgdfg dfgdgdgd, Oct 20 '14 at 12:48
@frgdgdfgdfgdgdgd you have to insert records with **NULL** values, check [this](http://stackoverflow.com/questions/1871331/php-mysql-insert-into-without-using-column-names-but-with-autoincrement-field) — chanchal, Oct 20 '14 at 12:53

Kevin · Accepted Answer · 2018-12-12T08:20:26.657

4

If this is just for experimental purposes yes you can:

INSERT INTO Persons VALUES (NULL, 'Lastname', 'Firstname', 'Address test', 'city of angels');

But I strongly urge you to not make it a habit of doing this. Always add columns, as eloquently stated by David's answer.

INSERT INTO Persons (LastName, FirstName, Address, City) VALUES ('Last', 'First', 'Add', 'City');

Fiddle

edited Dec 12 '18 at 08:20

answered Oct 20 '14 at 12:54

Kevin

41,694
12
53
70

1

@frgdgdfgdfgdgdgd You've said that before and did not define WHAT the error is. – Funk Forty Niner Oct 20 '14 at 12:56
1

Finally an answer that makes sense ;) Seems like this one http://stackoverflow.com/a/26465998/ knows something I don't. – Funk Forty Niner Oct 20 '14 at 12:57
@Fred-ii- still fiddling i guess. surprisingly, i don't know how it got those votes – Kevin Oct 20 '14 at 13:01
1

Has finally seen the error of his/her ways ;) Original answer http://stackoverflow.com/revisions/26465998/1 contained backticks instead of quotes. – Funk Forty Niner Oct 20 '14 at 13:11
@Fred-ii- now finished fiddling :). Lucky answer though, most downvoters don't retract their cast once they made up their mind. – Kevin Oct 20 '14 at 13:15
Lucky's the word. However, the edit didn't mention the fact that he used backticks instead of quotes, only states *"Sorry missed the NULL value for the primary key"*. Answers must be clear in edits. I know "you" know that ;) – Funk Forty Niner Oct 20 '14 at 13:17
@Fred-ii- lol, someone who cares enough will see the comments anyway. im just surprised the answer withstood until the end. even got an upvote lol. – Kevin Oct 20 '14 at 13:25
1

I just like to set the record(s) straight. ;) no more, no less. – Funk Forty Niner Oct 20 '14 at 13:37

score 3 · Answer 2 · edited May 23 '17 at 12:32

3

If you don't specify which columns are supposed to get those values, how do you expect the database to know? If you at least supply values for all columns then maybe the query engine can infer by the ordering of the columns, depending on the database. (Which, according to this question and its answers, seems to be supported in MySQL.)

But even then, that's not great practice. It creates all sorts of coupling between the schema and the code. By specifying the columns explicitly you can later add/change unrelated columns in the same table without affecting that query. But if you rely on the ordering of columns in the schema then any change to the schema will require changing every query, even ones which are logically unaffected.

It's simpler, more stable, and expresses intent more clearly to specify the columns explicitly.

edited May 23 '17 at 12:32

Community

1
1

answered Oct 20 '14 at 12:48

David

208,112
36
198
279

..yeah i know .but i just wanna know how can i implement the menthod explained here http://stackoverflow.com/questions/1871331/php-mysql-insert-into-without-using-column-names-but-with-autoincrement-field – frgdgdfg dfgdgdgd Oct 20 '14 at 12:51
@frgdgdfgdfgdgdgd: The very first answer to that question seems to provide an explanation. Use `NULL` for the `AUTO_INCREMENT` column and specify values for the remaining columns. (Some of which may also be `NULL`, of course.) – David Oct 20 '14 at 12:53
i have tried that but it didnt helped me ..can you please provide an sql fiddle example. – frgdgdfg dfgdgdgd Oct 20 '14 at 12:53
@frgdgdfgdfgdgdgd: What did you try and how did it fail? Update the question with this new information so people can help. – David Oct 20 '14 at 12:54

DrRoach · Answer 3 · 2014-10-20T12:58:48.257

2

Make sure you enter all column value in your insert.

INSERT INTO Persons VALUES (NULL, 'Last name', 'First name', 'Address', 'City');

Sorry missed the NULL value for the primary key

edited Oct 20 '14 at 12:58

answered Oct 20 '14 at 12:42

DrRoach

1,320
9
16

`VALUES (...` you sure about that? – Funk Forty Niner Oct 20 '14 at 12:44
-1 Well I am not. I think I know what I'm talking about. – Funk Forty Niner Oct 20 '14 at 12:46
Well then what is your problem? – DrRoach Oct 20 '14 at 12:46
it didnt helped ..it throws error ..it would be helpful if you provide fiddle example – frgdgdfg dfgdgdgd Oct 20 '14 at 12:47
@DrRoach I'll tell you what. You try your code exactly as you answered it ok? Then come back and tell me you're right; which I **highly** doubt. OP said it throws error, ask yourself why now. – Funk Forty Niner Oct 20 '14 at 12:48
Made the change, you just saying why would of been simpler – DrRoach Oct 20 '14 at 12:59
For crying outloud. Whoever is continually upvoting this, you don't know how SQL works. Dude, use quotes around values and not backticks `\`` - backticks are for columns and not values. That's the "problem". – Funk Forty Niner Oct 20 '14 at 12:59
1

If anyone should wonder why I said the above, is due to the original answer given http://stackoverflow.com/revisions/26465998/1 see the revisions. Just dotting the `i`'s and dotting the `t`'s, as it were. – Funk Forty Niner Oct 20 '14 at 13:13

score 0 · Answer 4 · answered Oct 20 '14 at 12:43

0

You have to specify columns, or insert value into each one of them.

If you have 6 columns, you can´t pass there 2 values without naming 2 columns, etc.

answered Oct 20 '14 at 12:43

pavel

26,538
10
45
61

score 0 · Answer 5 · answered May 19 '16 at 17:04

0

I think you can but you need to mentioned values as they appear in table record' structure. If you miss it you'll get wrong recording or query would fail.

answered May 19 '16 at 17:04

Ludus H

239
2
15

Insert values without mentioning the column name

5 Answers5