Menu
I would like to insert values into mysql innodb table Auto_Increment column.
I am loading some data from an old table to new table which has an identity, and need to preserve the existing values from the old table, so I need to preserve the existing Id values, but keep the column Auto_Increment for new values.
In MS T-SQL, I would start my insert query script with SET SET IDENTITY_INSERT MyTable ON and end the query with SET SET IDENTITY_INSERT MyTable OFF.
How can I do the same in MySQL?
459
To manually insert a new value into the Id column, we first must set the IDENTITY_INSERT flag ON as follows: SET IDENTITY_INSERT Students ON; To set the IDENTIT_INSERT flag ON we need to use the SET statement followed by the flag name and the name of the table.
Syntax for MySQLMySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. VALUES ('Lars','Monsen'); The SQL statement above would insert a new record into the "Persons" table.
In syntax, First, you must specify the name of the table. After that, in parenthesis, you must specify the column name of the table, and columns must be separated by a comma. The values that you want to insert must be inside the parenthesis, and it must be followed by the VALUES clause.
INSERT INTO Syntax Specify both the column names and the values to be inserted: INSERT INTO table_name (column1, column2, column3, ...)
Just do as usual:
INSERT INTO my_table(auto_inc_field, other_field) VALUES(8547, 'some value');
If the values are comming from another table, you may use:
INSERT INTO my_table(auto_inc_field, other_field)
SELECT auto_inc_field, other_field FROM other_table;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
