Use last inserted ID in another table

Question

I am inserting into two tables at the same time, tss_client and tss_details. First, I insert into tss_details, then using Details_ID from there, I insert into tss_client.

How to I get the most recent ID, from tss_details? I'm trying LAST_INSERT_ID() but it doest work

 addDetailsQry.prepare("INSERT INTO tss_details(Username, Password, Email, Description, etc..) "
                      "VALUES (:Username, :Password, :Email, :Description, etc..");

 addClientQry.prepare("INSERT INTO tss_client(Client_Name, Website, Contact_Details_ID)"
                     "VALUES (:Client_Name, :Website, :LAST_INSERT_ID()");

addDetailsQry.bindValue(":Username", username);
addDetailsQry.bindValue(":Password", password);
addDetailsQry.bindValue(":Email", email);
addDetailsQry.bindValue(":Description", description);

addClientQry.bindValue(":Client_Name", clientName);
addClientQry.bindValue(":VAT_Number", VATNumber);
addClientQry.bindValue(":Website", website);
addClientQry.bindValue(":Contact_Details_ID", /*what would i put here*/ );

if(addDetailsQry.exec())
{
    qDebug() << "Client Details added!";

    if(addClientQry.exec())
    {
        qDebug() << "Client added!";
    }
}

The ID column in tss_details is an identity.

I'm using ODBC and SQL Server version:

'Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Apr 29 2016 23:23:58 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows 10 Pro 6.3 (Build 14393: )

and I get:

[Microsoft][ODBC SQL Server Driver][SQL Server]'LAST_INSERT_ID' is not a recognized built-in function name.

score 0 · Answer 1 · edited May 23 '17 at 12:40

Depends on the SQL implementation, I don't know any general solution.

Here someone asked for the implementation of this in a Microsoft SQL Server: https://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id

PostgreSQL:

currval('SEQUENCE_IDENTIFIER'::regclass)

In PostgreSQL, the IDs come from "SEQUENCES", and you could use the following as documented in https://www.postgresql.org/docs/8.2/static/functions-sequence.html . Note that this only works if "nextval" has been called in this session, which is also mentioned in the link.

MySQL: (https://stackoverflow.com/questions/3837990/last-insert-id-mysql)

LAST_INSERT_ID()

Suit yourself.

Use last inserted ID in another table

1 Answers1