Error while running the following script

Error: Error occurred during SQL query execution

Reason:
SQL Error [1]: [SQLITE_ERROR] SQL error or missing database (near "SET": syntax error)

Script for test data creation:

SET NOCOUNT ON
DECLARE @counter int = 1

WHILE (@counter <= 100)
BEGIN
    DECLARE @deptno number = 'deptno '   RTRIM(@counter)
    DECLARE @name varchar2(50) = 'ABC '   RTRIM(@counter)
    DECLARE @location varchar2(50) = 'xyz'   RTRIM(@counter)   '-USA'
    
    INSERT INTO DEPARTMENTS 
    VALUES (@deptno, @name, @location)
    
    SET @counter = @counter  1
    
    IF (@counter = 0)
        PRINT RTRIM(@counter)   'rows inserted.'
END;

CodePudding user response：

SqLite doesn't support variables like in that script.
(Which looks like a T-SQL script)

But you can generate those records also via a recursive CTE.

WITH RECURSIVE RCTE AS (
  SELECT 1 as n
  UNION ALL
  SELECT n 1
  FROM RCTE
  WHERE n < 100
)
INSERT INTO DEPARTMENTS (
 deptno, 
 name, 
 location
)
SELECT n AS deptno
, ('ABC ' || n) AS name
, ('xyz ' || n || '-USA') AS location
FROM RCTE;

select * from DEPARTMENTS order by deptno desc limit 3;

Demo on db<>fiddle here