Multi-Insert Shorthand with Primary Key & Identity

I would like a shorthand (if possible) for inserting multiple records into a table which has a primary key and IsIdentity property. For example, say I have a table called 'People' with the following columns:

- ID (Primary Key, and Identity [i.e. autoincrementing])

- Name (not null)

- Email (not null)

An insert statement excluding the auto-incrementing ID column is perfectly valid, such as:

INSERT INTO People VALUES ('George', 'george@email.com')

But if I want to insert multiple values in the same statement, ideally that could be done something like this where I don't have to explicitly specify the column names:

INSERT INTO People VALUES (
   (auto, 'George', 'george@email.com'),
   (auto, 'Mary', 'mary@email.com')
)

The best solution I could find was something like this:

INSERT INTO People (
   SELECT 'George', 'george@email.com',
   UNION ALL
   SELECT 'Mary', 'mary@email.com'
)

I suppose you could argue, this is a somewhat meaningless pursuit, but I wanted the query itself to be extensible along with the table design. For example, if a column name changed, or more columns were added I wouldn't have to change this everywhere in the code.

Cheers :)开发者_开发技巧

You can insert multiple rows like this:

INSERT INTO `People` (`name`,`email`) VALUES ('George', 'George@test.com'),('Mary', 'LittleLamb@test.com');

EDIT:

mysql> create table `test`(
    -> `id` int(10) unsigned not null AUTO_INCREMENT,
    -> `name` varchar(255) not null default 'N/A/',
    -> `email` varchar(255) not null default 'N/A/',
    -> PRIMARY KEY(`id`) 
    -> )ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `test` VALUES (null, 'Name 1', 'Email 1'),(null, 'Name 2','Email 2');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from `test`;
+----+--------+---------+
| id | name   | email   |
+----+--------+---------+
|  1 | Name 1 | Email 1 |
|  2 | Name 2 | Email 2 |
+----+--------+---------+
2 rows in set (0.00 sec)

As long as your primary key is set to auto increment, you can nullify the field and it will auto set the value to the auto increment value.

That depends on what database you are using. Some databases allow multiple value sets:

insert into People values
(auto, 'George', 'george@email.com'),
(auto, 'Mary', 'mary@email.com')