how to add muliple rows with insert in order

Question

I have table that have the following columns:

id, state_id, name

the id is auto_increment

now I want to insert into it multiple rows at once as following:

insert into tbl (name, state_id)
         Values ('b',4121),
                ('a',4121);

what I want is that those values get added in order that correspond to the name so for example the first row will have id 1 and name a and second row will have id 2 and name b.

how to do this?

Answer 1

I believe that, in most cases, your specific example (two rows inserted via a single INSERT ... VALUES statement, will not produce the results you're asking for. If you listed the names in alphabetic order, then they'd get assigned as "a" = 1 and "b" = 2 - as you've shown it, you'll get "b" = 1 and "a" = 2.

That said, as others have noted, why do you care? Is it important somehow that the rows get specific ID values, or that ID values are sequential, with no gaps?

If so, then an auto-increment key may not be your best solution.

An auto-increment key works best when you don't care what the ID is, just that it's unique. As your primary key, an auto-increment column simply ensures that each new row will have a unique ID value, nothing else. That ID value may be used to tie rows in other tables back to this table; that's all the value needs to do if that's the case. If you want data from both tables, use a JOIN when you select them.

Often, your data may have other values that provide some sense of order (for instance, the name itself). Don't confuse order with the ID value; they're two separate things.

Yes, when a table has a clustered index, then the data is stored in order based on that index (which is usually, but not always, the primary key). However, that's only really useful for putting queries together.

The order of the data returned by a query is only guaranteed to be meaningful if the query has an ORDER BY clause. Just because the data is stored in a particular order does not mean that's the order the results of a SELECT query will come back in.

Plus, of course, if name in your first two rows happen to be "Aaron Aardvark" and "Zeb Zebra", and these get IDs 1 and 2, respectively - what happens when a third row with name "Penelope Parrot" is added? Now, the IDs are out of alphabetic order by name.

In the very limited case of:

• You'll only load data into your table once; and
• For some reason, you need the IDs to match the order of some other column in the table;

You can try sorting the data based on that column, and loading it in like that. From a single INSERT statement, the only INSERT you ever intend to run, the data should wind up getting assigned IDs in whatever order the data is presented.

That said, it's not the best way to run a database.

Answer 2

auto_increment ids are guaranteed to increment however might contain gaps due to various effects.

AnORDER BY id will always give you the order you added them (unless you change the id).

There are non-id mechanisms to a gapless rank in the output of the select.

Answer 3

insert into tbl (name, state_id)
select 'b',4121
union all
select 'a',4121
order by 1;