Door 20 Copying records to a new table with SQL

Welcome to door 20 of this advent calendar. We already saw in door 7 how to copy a part of a table. There, we read each field individually and then wrote it to a new table. But there is an easier way to do this. With the FM.InsertRecordQuery function. This function can transfer certain entries from one table to another.

MBS( "FM.InsertRecordQuery"; InsertFileName; InsertTableName; FieldNames; QueryFileName; 
   SQL Statement { ; Params... } )  

First, we specify the file name and table name of the table into which the data is to be inserted. This is followed by a list of the field names into which the data is to be written. Then comes the file name of the file on which the SQL query is to run, followed by the SQL query itself. If parameters are used in the SQL query, you can specify them afterwards.

Let's look at an example: We create a new table called Door 20 and want to copy all movies from the Movie table that were released between 2000 and 2009 into this empty table. Our Door 20 table has the fields Name, Director, Launch, and Generated_Sale. Our function then looks like this:

Set Variable [ $r ; Value: MBS("FM.InsertRecordQuery"; Get(FileName); "Door20"; 
   "Name¶Director¶Launch¶Generated_Sales"; Get(FileName); 
   "SELECT Name, Director, Launch, Generated_Sales FROM Movie WHERE Launch>=2000 AND Launch <=2009 ") ]

We can again solve this task with SQL.

INSERT INTO InsertTable (Field1, Field2…)
SELECT Field1, Field2…
FROM QueryTable
WHERE condition

The keyword here is INSERT INTO, followed by the table into which we want to insert the records. If we want to fill all fields from this table, we can write an * after it, but if we want to fill individual fields of this table with the records, we write the fields in brackets and separated by commas. Then we write our normal SQL query on the database that provides us with the data records.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "INSERT INTO Door20(Name, Director, Launch, Generated_Sales) 
   SELECT Name, Director, Launch,Generated_Sales 
   FROM Movie 
   WHERE Launch>=2000 AND Launch <=2009" ) ]

Here, too, the rows were copied into the new table.

That brings us to the end of today's door. I hope you enjoyed it and that you'll be back tomorrow for the 21st door.