Joshua
Joshua

Reputation: 51

SQL INSERT INTO returning autoincrement field

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

Answers (5)

fcr
fcr

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

marc_s
marc_s

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

Gabe
Gabe

Reputation: 50503

select scope_identity

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

Details here

Upvotes: 4

Brian Young
Brian Young

Reputation: 1778

In MySQL you can use LAST_INSERT_ID()

Upvotes: 3

Adam Porad
Adam Porad

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

Related Questions