WHILE query syntax in MonetDB

Question

I am using the latest version of MonetDB for Windows and I have installed it on Windows 7.

Hello guys, I have recently started looking into MonetDB. End goal is to prepare prototype to demonstrate the query performance difference between MonetDB and SQL Server.

I have went through the MonetDB SQL Reference documentation but it is not clear to me that how should I prepare WHILE query.

Create table query:

sql>CREATE TABLE TestString ( Name VARCHAR(40) NULL );

Declare and set the local variable:

sql>delcare rowcount;
sql>set rowcount=1;

While query I tried:

sql>WHILE rowcount<1000000
sql>DO
sql>insert TestString Values CAST(uuid() as VARCHAR(50))
sql>set rowcount=rowcount+1
sql>END WHILE

I did tried different variations in the while query by specifying indentation but it still not right.

Can someone help me with getting the right syntax or point me to any helpful user manual for this?

Hannes Mühleisen Hannes Mühleisen · Accepted Answer · 2015-01-25T08:27:36

MonetDB supports most of standard SQL-99. ~~WHILE is a part of Microsoft's Transact-SQL, which are not part of the standard and also not supported in MonetDB.~~

Got this to work as follows:

CREATE TABLE TestString ( Name String NULL );

CREATE PROCEDURE InsertTestString (nrow int)
BEGIN
    DECLARE rowcount int;
    SET rowcount = 0;
    WHILE (rowcount < nrow) DO
        INSERT INTO TestString VALUES (uuid());
        SET rowcount = rowcount + 1;
    END WHILE;
END;

CALL InsertTestString(100);

Apparently, the WHILE wants to be wrapped in a PROCEDURE or FUNCTION. Learned something today...

Another important note: Large numbers of single INSERT commands are not very fast in MonetDB. It is advisable to either wrap them in a transaction or use bulk loading.

WHILE query syntax in MonetDB

1 Answers