SQL Server script for SQLite

Question

I need to convert scripts for SQL Server, so I can use them for SQLite.

The scripts look like this:

DECLARE @var1 int = (SELECT Id FROM Table1 WHERE id = 'val1')
DECLARE @var2 int  = (SELECT Id FROM Table2 WHERE id = 'val2')

IF NOT EXISTS(SELECT * FROM Table3 WHERE id_1 = @var1 and id_2 = @var2)
    INSERT INTO Table3(id_1, id_2) VALUES (@var1, @var2)
GO

IF NOT EXISTS(SELECT * FROM Table4 WHERE id ='val3')
    INSERT INTO Table4(id, desc) values ('111', 'new entry')
GO

What is the best practice for executing these scripts in SQLite?

question from:https://stackoverflow.com/questions/65847713/sql-server-script-for-sqlite

Answer 1

SQLite does not support variables.

You can use a CTE that returns the values of the 2 variables as columns:

WITH cte(var1, var2) AS (
  SELECT (SELECT Id FROM Table1 WHERE id = 'val1'), (SELECT Id FROM Table2 WHERE id = 'val2') 
)
INSERT INTO Table3(id_1, id_2) 
SELECT var1, var2
FROM cte 
WHERE NOT EXISTS (SELECT * FROM Table3 t3 INNER JOIN cte c ON t3.id_1 = c.var1 AND t3.id_2 = c.var2);

INSERT INTO Table4(id, `desc`) 
SELECT '111', 'new entry'
WHERE NOT EXISTS (SELECT * FROM Table4 WHERE id ='val3')