Is there a way to name columns in an INSERT statement?

Question

When I do SELECT statements in PHP code I always select named columns, like:

SELECT id, name from users;

rather than using:

SELECT * from users;

This has the advantage of being more informative and readable, and also avoids problems later if new columns are added to the table.

What I'm wondering is, is it possible to use the same idea in an INSERT statement? I'm imagining it might be something like this:

INSERT into people values (id=1, name="Fred");

The syntax as I've shown in this example doesn't work, but I wonder if something equivalent is possible? If not, does anyone know why not? Is it a deliberate omission?

Ben

Answer 1

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

INSERT INTO people (id, name)
VALUES (1, 'Fred');

Answer 2

INSERT into people(id,name) values (1, 'Fred');

Answer 3

INSERT INTO table (column_1, column_2, column_3, ...)
VALUES (value_1, value_2, value_3, ...)

Answer 4

what you want is

INSERT INTO PEOPLE ( id, name ) VALUES ( 1, 'FRED' )

Answer 5

Try it this way: INSERT INTO people (id, name) VALUES (1, "Fred");

Answer 6

Use INSERT INTO <table> (col1, col2) VALUES (1,2).

Answer 7

the syntax for this case is

INSERT into people (id, name) values (1, 'Fred');

at least it works in the dbms i use (PostgreSQL)

Answer 8

That's syntax is possible with MySQL only. Afaik, other RDBMS doesn't allow that. Here's the syntax:

INSERT INTO games SET yr = 2012, city = 'London';

I wish PostgreSQL facilitated this kind of insert syntax

Standard ANSI syntax, however, would be

INSERT into people (yr, city) values (2012, 'London');

Answer 9

Using standard syntax, you can insert multiple rows at once:

INSERT INTO Table (column1,...,columnN) VALUES
(r1v1, ..., r1vN),
(r2v1, ..., r2vN),
(r3v1, ..., r3vN).

This is MUCH faster way of importing data, than one-row-a-time (especially in MySQL)! I don't think you can do the same using

INSERT INTO Table SET column1 = r1v1, ..., columnN = r1vN

syntax.

(From personal experience, inserting 300000 rows one row a time took several minutes. When I changed the script to use few insert statements only, each inserting several thousands of rows, it took only few seconds to import it all, MySQL 5.0).