In MySQL, if the two tables have exactly the same structure (or the target table contains all the columns in the source table) and do not want to list all fields one by one, you can useINSERT INTO ... SELECT * FROM ...Statement to copy data. This method does not require you to list all field names.
Here is an example of such a SQL statement:
INSERT INTO target_table SELECT * FROM source_table;
In this example,source_tableis the original table containing data, andtarget_tableIt is the table where you want to insert data. This SQL statement willsource_tableAll records in copy totarget_table。
If two tables are in the same database, you can use this statement directly. If the table is in a different database, you need to prefix the table name, for example:
INSERT INTO database2.target_table SELECT * FROM database1.source_table;
Please note that when using this method, the target tabletarget_tableshould already exist and have a source tablesource_tableSame structure. In addition, this method does not check for duplicate records when copying data, so if the target table has primary key or unique constraints, it may throw a duplicate key error.
Make sure you understand the structure and content of the data before doing such an action, and consider whether there is a possibility of violating any integrity constraints. Before operating in a production environment, it is best to test in a safe environment and ensure that there is a data backup.
If you want to import data from the old table into the new table, and there are two extra fields in the new table.
You can useINSERT INTO ... SELECTSentence to implement. Assume your old table name isold_table, the new table name isnew_table, the two additional fields in the new table arenew_column1andnew_column2. If both new fields have default values, you do not need to explicitly provide their values in the query.
Here is a basic SQL example that inserts all records from the old table into the new table:
INSERT INTO new_table (column1, column2, ..., columnN) SELECT column1, column2, ..., columnN FROM old_table;
Here,column1, column2, ..., columnNare column names in the old table, you should replace these column names with the actual column names in the old table. This command assumes that the data types and order of the corresponding columns in the old and new tables are the same.
If the column names or order in your new table are different from the old table, you need toSELECTThe order and name of the columns are clearly specified in the statement to ensure that they correspond correctly. For example:
INSERT INTO new_table (old_column1, old_column2, ..., old_columnN, new_column1, new_column2) SELECT old_column1, old_column2, ..., old_columnN, DEFAULT, DEFAULT FROM old_table;
In the above SQL statement,DEFAULTKeywords are used to indicate fornew_column1andnew_column2The default values set in the new table should be used.
Make sure to back up the database before performing these operations in case of accidental deletion or damage to the data. At the same time, for any task involving data operations, make sure you have sufficient permissions and that the database will not be accessed frequently when performing these operations to avoid potential data conflicts.
If you don't want to enumerate all column names, and the two more fields in the new table have default values than the old table, you can simplify your SQL statement and omit the column name section. As long as the extra fields in the new table have default values, you can do this:
INSERT INTO new_table SELECT *, DEFAULT, DEFAULTFROM old_table;
In this example,*Indicates that all columns in the old table are selected.DEFAULTKeywords are used for two additional fields in the new table, which will use the default values specified in the table definition.
Note that this approach is only valid if the first N columns of the new table (N is the number of columns of the old table) are exactly the same as the columns of the old table. If the column order or column type of the new table is different from the old table, you may need to specify the column name explicitly to ensure the correctness of the data.
Also, before doing such an action, make sure to test the SQL statement to verify that it works as expected, especially before operating on a production database. If you are not sure, you can try it first in the test environment.
In some cases, if your database table is very large, it is recommended to insert data in batches to avoid potential performance issues.
This is the end of this article about the implementation example of the data old table leading to new tables in mysql. For more related contents of the old table leading to new tables in mysql, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!