How to Insert New Row to Database with Auto_Increment Column Without Specifying Column Names

How to insert new row to database with AUTO_INCREMENT column without specifying column names?

For some databases, you can just explicitly insert a NULL into the auto_increment column:

INSERT INTO table_name VALUES (NULL, 'my name', 'my group')

PHP MYSQL - Insert into without using column names but with autoincrement field

Just use NULL as your first value, the autoincrement field will still work as expected:

INSERT INTO tblname VALUES (NULL, ... 32 Fields ... )

How to insert data to MySQL with auto-incremented column(field)?

In order to take advantage of the auto-incrementing capability of the column, do not supply a value for that column when inserting rows. The database will supply a value for you.

INSERT INTO test.authors (
   instance_id,host_object_id,check_type,is_raw_check,
   current_check_attempt,max_check_attempts,state,state_type,
   start_time,start_time_usec,end_time,end_time_usec,command_object_id,
   command_args,command_line,timeout,early_timeout,execution_time,
   latency,return_code,output,long_output,perfdata
) VALUES (
   '1','67','0','0','1','10','0','1','2012-01-03 12:50:49','108929',
   '2012-01-03 12:50:59','198963','21','',
   '/usr/local/nagios/libexec/check_ping  5','30','0','4.04159',
   '0.102','1','PING WARNING -DUPLICATES FOUND! Packet loss = 0%, RTA = 2.86 ms',
   '','rta=2.860000m=0%;80;100;0'
);

SQL-How to Insert Row Without Auto incrementing a ID Column?

If you are in Microsoft SQL Server, you can "turn off" the autoIncrementing feature by issuing the statement Set Identity_Insert [TableName] On, as in:

  Set Identity_Insert [TableName] On
  -- --------------------------------------------
  Insert TableName (pkCol, [OtherColumns])
  Values(pkValue, [OtherValues])
  -- ---- Don't forget to turn it back off ------
  Set Identity_Insert [TableName] Off

Insert rows into a table with a increment column without auto increment

Get the maximum value and add it to a ROW_NUMBER() column during the select.

 (
   ID      int primary key
 , name    nvarchar(100)
 , Address nvarchar(1000)
 );

INSERT INTO Person
VALUES
      (122, 'John Doe', 'Some Address 123')
    , (123, 'Homer Simpson', 'Some Address 456')
    , (124, 'Jane Doe', 'Some Address 789')
    , (125, 'Bo Katan', 'Some Address 101112');
    
DECLARE @Persons TABLE
 (
   ID      int primary key
 , name    nvarchar(100)
 , Address nvarchar(1000)
 );
 
INSERT INTO @Persons
VALUES
      (2, 'Quinn Amaro','New Address a')
    , (3, 'Elenor Barreras','New Address B')
    , (4, 'Mckinley Dart','New Address c')
    , (5, 'Ronnie Tank','New Address D')
    , (6, 'Woodrow Creek','New Address e')
    , (7, 'Brittany Patlan','New Address F')
    , (8, 'Len Venzon','New Address g')
    , (9, 'Ila Goodlow','New Address H')
    , (10, 'Velma Tallarico','New Address i')
    , (11, 'Blossom Hanney','New Address J');

INSERT INTO Person (ID, name, Address)    
SELECT
      ID = ROW_NUMBER() OVER(ORDER BY ID) + (SELECT MAX(ID) FROM Person)
    , name
    , Address
FROM @Persons;

SELECT *
FROM Person;

Example db fiddle

MYSQL: how to insert statement without specifying col names or question marks?

MySQL's syntax for INSERT is documented here: http://dev.mysql.com/doc/refman/5.7/en/insert.html

There is no wildcard syntax like you show. The closest thing is to omit the column names:

INSERT INTO MyTable VALUES (...);

But I don't recommend doing that. It works only if you are certain you're going to specify a value for every column in the table (even the auto-increment column), and your values are guaranteed to be in the same order as the columns of the table.

You should learn to use code to build the SQL query based on arrays of values in your application. Here's a Python example the way I do it. Suppose you have a dict of column: value pairs called data_values.

placeholders = ['%s'] * len(data_values)
sql_template = """
INSERT INTO MyTable ({columns}) VALUES ({placeholders})
"""
sql = sql_template.format(
    columns=','.join(keys(data_values)),
    placeholders=','.join(placeholders)
)
cur = db.cursor()
cur.execute(sql, data_values)

Insert values without mentioning the column name

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

Insert rows in table without affecting AUTO_INCREMENT in MariaDB

MySQL and MariaDB actually enforce the restriction AUTO_INCREMENT > MAX(id)

See ALTER TABLE Syntax

You cannot reset the counter to a value less than or equal to the value that is currently in use. For both InnoDB and MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum AUTO_INCREMENT column value plus one.

You can use ALTER TABLE to set the AUTO_INCREMENT to any value higher than MAX(id) if you would like to store higher values, however you cannot set it to a lower value than one of the rows currently in the table.

If you need to create rows in a "gap", with lower IDs than the AUTO_INCREMENT value, you would need to explicitly specify the id value in your INSERT. But if a process beyond your control is inserting rows and not specifying the IDs then they are always going to obtain IDs higher than everything else currently in the table.

The only thing I can suggest, if you are able to adjust what IDs are used for what, is that you reserve low IDs for your purposes (so use, say, 1 to 10,000 instead of 50,000,000 to 50,009,999), set the AUTO_INCREMENT to 10,001 and then let the outside process use the higher IDs - this would work just fine provided you don't run out of space.

For a longer term solution, consider switching to UUIDs - though you would need to modify the process that is outside your control for this.

---

Related Topics