Advanced Database – Create, run a query to save selected data as a new table.

Imagine we want to copy all of the records with a job number of 10 and above from the ‘Jobs’ table into a new table called ‘Recent Jobs’

Of course, we could do this manually by creating a new table and entering the data records one by one.

This is time consuming, so we prefer to use an SQL statement to do the job for us.

13 New Table

Select Tools->SQL…

14 SQL Statement New Table

Type in the command: SELECT * INTO “Recent Jobs” FROM “Jobs” WHERE “Job No”>=10

This selects * fields (wildcard to represent all fields, rather than typing them all out) into a new table called ‘Recent Jobs’, which is automatically created, taking all records from ‘Jobs’ tables where the job number is greater than or equal to 10.

Click Execute.

15 Execute

Close the SQL window. Click on the Tables section of the main window. You may notice that there is no new table called ‘Recent Jobs’.

We first have to select View-> Refresh tables in order to see the newly created table.

Open the new table by double clicking in the Tables section.

16 New Table Executed

The SQL statement we executed has copied all of the records with a Job No of 10 and above and placed them into the new table.

If you check the Jobs table, the records will still be present in there.

If you wanted to use this as an archive tool for instance, you could include a delete command in the SQL statement, or in a separate one, to remove the records from the original table.

In general, to save selected data as a new table, use the SQL syntax:

SELECT columns INTO new_table
FROM old_table
WHERE some_condition