CODEWITHSUNDEEP
phpsql
Dave Maison
Dave Maison

Reputation: 417

SQL Insert into Auto_Increment column

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

  1. object.table('table_name') fills an array with the column names for 'table_name' pulled out of the table_schema

  2. object.insert(data_array) runs an insert statement into 'table_name' using the column array and the data_array

  3. object.select('where') returns everything in 'table_name' using a where statement input or nothing at all

I did this because i'm lazy and I wanted to make my code prettier. However, the schema lookup returns the ID columns from my tables, which is fine because sometimes I need it returned with object.select(). However, it makes object.insert() kinda difficult.

Is there some SQL functionality I don't know about that allows you to insert into an auto incrememented column accurately or is there a more simple solution right in front of my eyes and I'm just being a complete moron.

Upvotes: 4

Views: 245

Answers (3)

Void Ray
Void Ray

Reputation: 10199

Not sure which rdbms you are using, but in SQL server, you can allow inserts into identity column by: 

SET IDENTITY_INSERT dbo.MyTable ON

But I'm not sure why you would want to do that...

Instead, when you do: object.table('table_name'), collect more than just column names, e.g. ColumnName, DataType, IsIdentity, etc. It can by multi-dimensional array, or a class.

Then on insert/select, you can filter out IsIdentity's column. Knowing data types can also be helpful when constructing inserts.

Upvotes: 1

AlexP
AlexP

Reputation: 9857

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

Upvotes: 1

blearn
blearn

Reputation: 1208

Auto incremented columns cannot be inserted into. You can perform your insert (bypassing the auto-incremented field) and just accept the automatically-generated id...

Upvotes: 2

Related Questions