SQL INSERT INTO returns auto-increment field

Question

I have been a C ++ desktop programmer for a long time, new to SQL. I need to insert into a table with an auto-increment field, and this sql statement will return a new value for the auto-increment field.

Sort of:

INSERT INTO Entrys ('Name','Description') VALUES ('abc','xyz') SELECT Entrys.EntryID WHERE EntryID=[THE ONE JUST INSERTED!] 

Sorry for being a noob.

Answer 1

select scope_identity

 insert into table values ('string value');select scope_identity() 

Details here

Answer 2

In MySQL you can use LAST_INSERT_ID()

Answer 3

Assuming you are using SQL Server, you can use scope_identity to return the "last identity value inserted in the identifier column in Scope - this is a module: stored procedure, trigger, function or package. Therefore, the two statements are in the same scope if they are in the same stored procedure, function or batch. "

 INSERT INTO Entrys ('Name','Description') VALUES ('abc','xyz'); SELECT SCOPE_IDENTITY() 

Answer 4

In SQL Server, you can also use the OUTPUT clause in an INSERT statement:

 INSERT INTO Entrys('Name', 'Description') OUTPUT Inserted.EntryID VALUES ('abc','xyz') 

The new value of the IDENTITY field will be displayed.

Answer 5

As pointed out by @marc_s for SQL Server, in PostgreSQL you can get the same behavior:

 INSERT INTO Entrys ('Name','Description') VALUES ('abc','xyz') RETURNING EntryID; 

RETURNING is also very useful when you insert multiple tuples and you want all the generated identifiers for each row.