Creating a record with SELECT You may have noticed that the INSERT statement is different from the DELETE statement and the Update statement. It only operates a record at a time. However, there is a way to make the INSERT statement to add multiple records 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_SecondFrom AnothertableWhere Another_First = 'Copy ME!'
This statement records from anothertable to myTable. Only the value of the field another_first in the table another_first is' Copy ME! 'Records were copied. This form of INSERT statement is very useful when establishing a backup for recording in a table. Before deleting records in a table, you can copy them to another table with this method. 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, which contains all the data of 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, just specify the field you want to copy in the list of fields. In addition, 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 is equal to the first_column field of the record of 'Copy ME!'. SELECT First_Column Into nextTableFrom MyTableWhere Second_Column = 'Copy ME!'
It is difficult to use SQL to modify the table that has been established. For example, if you add a field to a table, there is no easy way to remove it. Also, if you accidentally give the data type of a field, you will have no way to change it. However, using the SQL statement described in this section, you can bypass these two questions. 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 do not include the fields to be deleted. This allows you to delete this field and retain data that 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 field. After you create the table, you can use the UPDATE statement and the SELECT statement to copy all the data in the original table to the new table. In this way, you can modify the structure of the table and save the original data.
