How to get the ID of new row in SQL?

Question

I want to fill a column with a format using its ID.

My table:

CREATE TABLE "TEST" 
(
    "ID"    INTEGER,
    "Formatted_Column"  TEXT,
    PRIMARY KEY("ID" AUTOINCREMENT)
);

I want to do:

INSERT INTO TEST (Formatted_Column) VALUES ('U' + this_ID);

INSERT INTO TEST (Formatted_Column) VALUES ('U' + this_ID);

I want the output to be:

ID	Formatted_Column
1	U1
2	U2

What methods can help me?

Note: I tried to use last_insert_rowid()+1, but I think it's kind of spaghetti code (: .

My database currently SQLite, but I will change it to MySQL or SQL Server later.

I will use an online database with multi-users.

Thank you

Answer 1

If your version of SQLite is 3.31.0+ you can define Formatted_Column as a generated column: (VIRTUAL or STORED):

CREATE TABLE "TEST" (
  "ID" INTEGER,
  "Formatted_Column" TEXT GENERATED ALWAYS AS ('U' || ID) STORED,
  PRIMARY KEY("ID" AUTOINCREMENT)
);

After you insert 2 rows:

INSERT INTO TEST (ID) VALUES (NULL), (NULL);

you will have:

ID	Formatted_Column
1	U1
2	U2