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

What is the reason to duplicate this information? Primary key should be used for identification purposes only. Databases mentioned at the end support calculated columns, but I hope you do not a calculated column — astentx, Jul 03 '21 at 09:12
I know. I have my own scenario that requires me to duplicate The ID. — Baghdadi, Jul 03 '21 at 09:14
Does this answer your question? [How to retrieve the last autoincremented ID from a SQLite table?](https://stackoverflow.com/questions/2127138/how-to-retrieve-the-last-autoincremented-id-from-a-sqlite-table) — derpirscher, Jul 03 '21 at 09:16
For SQL Server have a look at for instance this question https://stackoverflow.com/questions/42648/sql-server-best-way-to-get-identity-of-inserted-row — derpirscher, Jul 03 '21 at 09:18

score 1 · Accepted Answer · answered Jul 03 '21 at 09:23

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

1 Answers1