Insert SQL into Auto_Increment column

Question

Basically, I have a PHP class for generic SQL statements:

• object.table('table_name') populates the array with column names for 'table_name' pulled from table_schema

• object.insert(data_array) runs the insert statement on 'table_name' using an array of columns and data_array

• object.select('where') returns everything in 'table_name' , using the input of the where statement or nothing at all

I did this because I am lazy and I wanted to make my code more beautiful. However, searching for a schema returns ID columns from my tables, which is great, because sometimes I need it to be returned using object.select() . However, this makes it difficult.

Is there some kind of SQL functionality that I don’t know about that allows you to precisely insert into an auto-increment column or is there a simpler solution right in front of my eyes and I'm just a total jerk.

Answer 1

Auto-growing columns cannot be inserted. You can perform your insertion (bypassing the automatically increasing field) and just accept the automatically generated identifier ...

Answer 2

You need to omit the id column from the insert statement and use last_insert_id() to retrieve the last key inserted

Answer 3

Not sure which rdbms you are using, but on the SQL server you can allow insertions into the identifier column:

 SET IDENTITY_INSERT dbo.MyTable ON 

But I'm not sure why you would like to do this ...

Instead, when you do: object.table ('table_name'), collect more than just column names, for example. ColumnName, DataType, IsIdentity, etc. It can be a multidimensional array or class.

Then insert / select, you can filter the IsIdentity column. Knowing data types can also be useful when building inlays.