select| Create records and tables with select
You may have noticed that the INSERT statement is a little different from the DELETE statement and the UPDATE statement, and it only operates one record at a time. However, there is a way to make the INSERT statement add more than one record at a time. To do this, you need to combine the INSERT statement with the SELECT statement, like this:
INSERT MyTable (First_column,second_column)
SELECT Another_first,another_second
From anothertable
WHERE another_first= ' Copy me! '
This statement is recorded from the anothertable copy to the mytable. Only the value of field another_first in table anothertable is ' copy me! ' Records before being copied.
This form of INSERT statement is useful when a backup is established for a record in a table. Before you delete records from a table, you can copy them to another table in this way.
If you need to copy the entire table, you can use the SELECT INTO statement. For example, the following statement creates a new table named NewTable that contains all the data for the table mytable:
SELECT * into newtable from mytable
You can also specify that only specific fields are used to create this new table. To do this, simply specify the field you want to copy in the field list. Alternatively, you can use the WHERE clause to limit the records copied to the new table. The following example only copies the value of the field second_columnd equal to ' copy me! ' The First_column field of the record.
SELECT First_column into newtable
From MyTable
WHERE second_column= ' Copy me! '
It is difficult to modify an already established table using SQL. For example, if you add a field to a table, there is no easy way to get rid of it. In addition, if you accidentally give a field a data type wrong, you will have no way to change it. However, using the SQL statements that are described in this section, you can bypass these two issues.
For example, suppose you want to delete a field from a table. Using the SELECT INTO statement, you can create a copy of the table, but it does not contain the field you want to delete. This allows you to delete both the field and the data you don't want to delete.
If you want to change the data type of a field, you can create a new table that contains the correct data type fields. Once you've created the table, you can use both the UPDATE statement and the SELECT statement to copy all the data from the original table to the new table. In this way, you can modify the structure of the table and save the original data.
