Reputation: 51
I'm a long time desktop app C++ programmer new to SQL. I need to insert into a table with an autoincrment field, and have that sql statement return the new value for the autoincrement field.
Something LIKE:
INSERT INTO Entrys ('Name','Description')
VALUES ('abc','xyz')
SELECT Entrys.EntryID WHERE EntryID=[THE ONE JUST INSERTED!]
Sorry for being a noob.
Upvotes: 5
Views: 17503
Reputation: 170
As @marc_s said for SQL Server, in PostgreSQL you can obtain the same behaviour with:
INSERT INTO Entrys ('Name','Description')
VALUES ('abc','xyz')
RETURNING EntryID;
RETURNING is also really useful when you're inserting several tuples and you want all the generated IDs for each of the rows.
Upvotes: 0
Reputation: 754688
In SQL Server, you can also use the OUTPUT clause on your INSERT statement:
INSERT INTO Entrys('Name', 'Description')
OUTPUT Inserted.EntryID
VALUES ('abc','xyz')
This will output the newly created IDENTITY field value.
Upvotes: 4
Reputation: 50503
select scope_identity
insert into table values ('string value');select scope_identity()
Details here
Upvotes: 4
Reputation: 14471
Assuming that you're using SQL Server, you can use scope_identity to return "the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, 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()
Upvotes: 4