Base+BASIC : Inserting multiple rows (huge text file) into a table

[lonk]

From the sample code and if I had no choice and had to place all data of all rows of AlternativeCode from S-0004 to S-9999 into code and then insert those multiple rows into a table. Is dumping all data into sSQL possible? Are there any ways to write code for this purpose ?

sSQL = "INSERT INTO ""AlternativeCode"" (ID, ""AlternativeCode"", ""Code1"", ""Code2"", ""Code3"", ""Code4"", ""Code5"", ""Code6"")" & _
       " VALUES ('" & "6" & "','" & "S-0004" & "', '" & "02" & "', '" & "02" & "', '" & "02" & "', '" & "02" & "', '" & "02" & "', '" & "02" & "')"
.
.
.
.
       " VALUES ('" & "6" & "','" & "S-9999" & "', '" & "02" & "', '" & "02" & "', '" & "02" & "', '" & "02" & "', '" & "02" & "', '" & "02" & "')"
oStatement.executeUpdate(sSQL)

[RobertG]

Do you really want to insert every row with the same “ID” = ‘6’?

FOR i=4 TO 9999
si = RIGHT("000"&i, 4)
si = "S-"&si
sSQL = "INSERT INTO ""AlternativeCode"" (ID, ""AlternativeCode"", ""Code1"", ""Code2"", ""Code3"", ""Code4"", ""Code5"", ""Code6"")" & _
       " VALUES ('" & "6" & "','" & si & "', '" & "02" & "', '" & "02" & "', '" & "02" & "', '" & "02" & "', '" & "02" & "', '" & "02" & "')"
oStatement.executeUpdate(sSQL)
NEXT

But it won’t work because ID is always the same. Could be created with auto value or another counter in this loop.

[lonk]

Sorry for my disgusting sample, I would like to add a huge text file into code.
New example:

INSERT INTO table1 (FirstName, LastName)
VALUES
    ('Freddy', 'Smithson'),
    ('Johnny', 'Smithy'),
    ('Mitchel', 'Smither'),
    ('Roberto', 'Blacksmith');

From the above example, if I’d like to add 1,000 rows and place those 1,000 rows into code. I think variable namely sSQL could not do that.
I have an idea of adding 1,000 rows into an array first and then use looping to read from the array and insert into the table row by row.

[RobertG]

Which database do you want to use? With internal HSQLDB and the internal Firebird it is impossible to insert more than 1 row with 1 command.

The table has to be inserted row by row.

[lonk]

I use embedded Firebird + LibreOffice 7.3.3.2 + Ubuntu 22.04 LTS.
Now I got the way to handle it by doing this:

Dim ArrayField1(), ArrayField2()
ArrayField1 = Array("Freddy", "Johnny", "Mitchel",  "Roberto")
ArrayField2 = Array(Smithson", "Smithy", "Smither", "Blacksmith")

For i = 0 To 3 ' = 4 rows
    sSQL = "INSERT INTO ""AllStaff"" (""FirstName"", ""LastName"")" & _
    " VALUES (ArrayField1(i), ArrayField2(i) "')"
    oStatement.executeUpdate(sSQL)
Next