[sql] Declare variable in SQLite and use it

I want to declare a variable in SQLite and use it in insert operation.

Like in MS SQL:

declare @name as varchar(10)
set name = 'name'
select * from table where name = @name

For example, I will need to get last_insert_row and use it in insert.

I have found something about binding but I didn't really fully understood it.

Try using Binding Values. You cannot use variables as you do in T-SQL but you can use "parameters". I hope the following link is usefull.Binding Values

I appreciate that the other solutions do not depend on any other software tool, but why not just use another programming language that can interface to SQLite such as C#, C++, Go, Haskell, Java, Lua, Python, or Rust?

I found one solution for assign variables to COLUMN or TABLE:

conn = sqlite3.connect('database.db')
cursor=conn.cursor()
z="Cash_payers"   # bring results from Table 1 , Column: Customers and COLUMN 
# which are pays cash
sorgu_y= Customers #Column name
query1="SELECT  * FROM  Table_1 WHERE " +sorgu_y+ " LIKE ? "
print (query1)
query=(query1)
cursor.execute(query,(z,))

Don't forget input one space between the WHERE and double quotes and between the double quotes and LIKE

Herman's solution works, but it can be simplified because Sqlite allows to store any value type on any field.

Here is a simpler version that uses one Value field declared as TEXT to store any value:

CREATE TEMP TABLE IF NOT EXISTS Variables (Name TEXT PRIMARY KEY, Value TEXT);

INSERT OR REPLACE INTO Variables VALUES ('VarStr', 'Val1');
INSERT OR REPLACE INTO Variables VALUES ('VarInt', 123);
INSERT OR REPLACE INTO Variables VALUES ('VarBlob', x'12345678');

SELECT Value
  FROM Variables
 WHERE Name = 'VarStr'
UNION ALL
SELECT Value
  FROM Variables
 WHERE Name = 'VarInt'
UNION ALL
SELECT Value
  FROM Variables
 WHERE Name = 'VarBlob';

Herman's solution worked for me, but the ... had me mixed up for a bit. I'm including the demo I worked up based on his answer. The additional features in my answer include foreign key support, auto incrementing keys, and use of the last_insert_rowid() function to get the last auto generated key in a transaction.

My need for this information came up when I hit a transaction that required three foreign keys but I could only get the last one with last_insert_rowid().

PRAGMA foreign_keys = ON;   -- sqlite foreign key support is off by default
PRAGMA temp_store = 2;      -- store temp table in memory, not on disk

CREATE TABLE Foo(
    Thing1 INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
);

CREATE TABLE Bar(
    Thing2 INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    FOREIGN KEY(Thing2) REFERENCES Foo(Thing1)
);

BEGIN TRANSACTION;

CREATE TEMP TABLE _Variables(Key TEXT, Value INTEGER);

INSERT INTO Foo(Thing1)
VALUES(2);

INSERT INTO _Variables(Key, Value)
VALUES('FooThing', last_insert_rowid());

INSERT INTO Bar(Thing2)
VALUES((SELECT Value FROM _Variables WHERE Key = 'FooThing'));

DROP TABLE _Variables;

END TRANSACTION;

For a read-only variable (that is, a constant value set once and used anywhere in the query), use a Common Table Expression (CTE).

WITH const AS (SELECT 'name' AS name, 10 AS more)
SELECT table.cost, (table.cost + const.more) AS newCost
FROM table, const 
WHERE table.name = const.name

SQLite WITH clause