Difference between insert table and create table as, insertcreate

Difference between insert table and create table as, insertcreate

Author: iamlaosong

First, the biggest difference is that the two are different types of statements. The former is DML statements (data operation language, SQL processing data, and other operations collectively referred to as data operation language ), after the task is completed, it must be submitted to take effect. The latter is a DDL Statement (Data Definition Language, used to define and manage the languages of all objects in the SQL database). After the execution, it takes effect directly and does not provide rollback, high efficiency. Secondly, the functions are different. The former only inserts data and must be created first. The latter completes table creation and data insertion.

Insert into table1 (columns1, columns2.) select columns1, columns2-from table2;
Insert the query result from table2 to table1, provided that table1 and table2 already exist;
 
Create. as .. select generally has the following three methods:
1. create table table1 as select * from table2 where 1 = 2;
Create a table with the same structure as table2, and only copy the structure without copying data;
2. create table table1 as select * from table2;
Create a table with the same structure as table2, and copy the data at the same time;
3. create table table1 (columns_a, columns_ B) as select columns1, columns2-from table2;
Create a table with the same structure as table2. Copy the structure and copy data, but specify the column name of the new table. This format is also used to copy some fields of table 2;

If the following two formats follow the appropriate query conditions, you can copy the data that meets the conditions to the new table. For example:

Create table table1 as select * from table2 where columns1> = 1;

If possible, we recommend that you use the second method. This method not only creates tables and inserts data at a time, but also has high efficiency, especially when the copied data volume is large, this prevents a large amount of data from waiting for submission in the rollback space.