Detailed explanation of two table copy statements: SELECT INTO and INSERT INTO SELECT (Differences between SQL database and Oracle database)

1. INSERT INTO SELECT statement

The statement format is: Insert into Table2(field1,field2,...) select value1,value2,... from Table1

Or: Insert into Table2 select * from Table1

Note: (1) The target table Table2 must exist, and the fields field, field2... must also exist.

(2) Note the primary key constraint of Table2. If Table2 has a primary key and it is not empty, then field1, field2, etc. must include the primary key.

(3) Pay attention to the syntax. Do not add values ​​to confuse it with the SQL for inserting a piece of data. Do not write it as:

Insert into Table2(field1,field2,...) values ​​(select value1,value2,... from Table1)

Since the target table Table2 already exists, we can insert constants in addition to the fields of the source table Table1. Here is an example:

 --1. Create a test table create TABLE Table1 
 ( 
 a varchar(10), 
 b varchar(10), 
 c varchar(10)
 )
 create TABLE Table2 
 ( 
 a varchar(10), 
 c varchar(10), 
 d int
 )

 --2. Create test data Insert into Table1 values('赵','asds','90') 
 Insert into Table1 values('money','asds','100') 
 Insert into Table1 values('孙','asds','80') 
 Insert into Table1 values('李','asds',null) 

 select * from Table2 

--3. INSERT INTO SELECT statement copies some columns and constant values ​​of table data Insert into Table2 (a, c, d) select a,c,5 from Table1

Or: Insert into Table2 select * from Table1

--4. Display the updated results select * from Table2 

 --5. Delete the test table drop TABLE Table1 
 drop TABLE Table2

2. SELECT INTO FROM Statement

The statement format is: SELECT vale1, value2 into Table2 from Table1

The target table Table2 is required not to exist, because Table2 will be automatically created during insertion, and the specified field data in Table1 will be copied to Table2. Here is an example:

--1. Create a test table create TABLE Table1 
 ( 
 a varchar(10), 
 b varchar(10), 
 c varchar(10)
 )

 --2. Create test data Insert into Table1 values('赵','asds','90') 
 Insert into Table1 values('money','asds','100') 
 Insert into Table1 values('孙','asds','80') 
 Insert into Table1 values('李','asds',null) 

 --3.SELECT INTO FROM statement creates table Table2 and copies data select a,c INTO Table2 from Table1 
 
 --4. Display the updated results select * from Table2 

 --5. Delete the test table drop TABLE Table1 
 drop TABLE Table2

Note: If you execute this statement in SQL/Plus or PL/SQL, you will get an "ORA-00905: missing keyword" error. This is due to the difference between PL/Sql and T-SQL.
This statement is normal in T-SQL, but the explanation in PL/SQL is:
select..into is part of PL/SQL language which means you have to use it inside a PL/SQL block. You can not use it in a SQL statement outside of PL/SQL.
That is, it cannot be executed as a single SQL statement and is generally used in a PL/SQL program block.

If you want to implement this function in PL/SQL, you can use Create table newTable as select * from ...:
For example: create table NewTable as select * from ATable;

NewTable is the same as ATable except that it has no keys.

---------SQL SELECT INTO Syntax Introduction
The SQL SELECT INTO statement can be used to create a backup copy of a table.
SELECT INTO Statement
The SELECT INTO statement selects data from one table and inserts the data into another table.
The SELECT INTO statement is often used to create backup copies of tables or to archive records.
SQL SELECT INTO syntax You can insert all columns into a new table:
SELECT * INTO new_table_name [IN externaldatabase] FROM old_table_name
Or insert only the desired columns into the new table:
SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_tablename
SQL SELECT INTO Example - Make a backup copy The following example makes a backup copy of the "Persons" table:
SELECT * INTO Persons_backup FROM Persons
The IN clause can be used to copy a table into another database:
SELECT * INTO Persons IN 'Backup.mdb' FROM Persons
If we want to copy certain fields, we can list them after the SELECT statement:
SELECT LastName,FirstName
INTO Persons_backup
FROM Persons
SQL SELECT INTO Example - with WHERE clause We can also add a WHERE clause.
The following example creates a table named "Persons_backup" with two columns by extracting information about people living in "Beijing" from the "Persons" table:
SELECT LastName,Firstname INTO Persons_backup FROM Persons WHERE City='Beijing'
SQL SELECT INTO Example - Joined tables It is also possible to select data from more than one table.
The following example creates a new table called "Persons_Order_Backup" that contains information from both the Persons and Orders tables:
SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P

Comparison between select into in mysql and select into in sql <br /> Now there is a table called student, and I want to copy the data in this table to a new table called dust.

answer 01:
create table dust select * from student; //Used when no new table dust is created before copying
answer 02:
insert into dust select * from student; //If a new table dust has been created

Now use the select..into.. statement to achieve the above stuff.

MySQL does not support Select Into statements to directly back up table structures and data. There are several methods that can be used instead. There are also other methods that can be used, which are summarized as follows:

Method 1:

MYSQL does not support:
Select * Into new_table_name from old_table_name; This is an alternative method of usage in SQL Server:
Create table new_table_name (Select * from old_table_name);

Method 2:

1. Back up the table structure and data first
#Export command -u username -p password -h host IP address database name table name 1 > export file.sql
mysqldump -uroot -proot -h192.168.0.88 ok_db oktable2 > ok_db.sql

2. Modify the name of the backup table
3. Log in to MySQL
4. Select the database
5.Execute: Source the path of the backup table, such as: Source d:/ok_db.sql and press Enter.
6. Done.

MySQL Select into outfile is used to export the specified query data to a file as follows:

1. Export all the data in the table to outfile.txt in the root directory of drive C as follows:
Select * into outfile 'c://outfile.txt' from test;

2. Export the data of the specified query condition 2005-06-08 in the table to outfile1.txt in the root directory of drive C as follows:
Select * into outfile 'c://outfile.txt' from test where beginDate='2008-06-08';

mysql> load data local infile "d:/gpsdata.txt" into table positiondata fields terminated by ';' (userid,latitude,longitude,altitude,speed,innerid,repo
rttime,status);

LOAD DATA [LOW_PRIORITY CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char' ]
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = eXPr,...)]

fields and lines are in front, (col_name_or_user_var, ...) are in the back. If you put the attributes you want to write directly after the table name when you use it, this is incorrect. You must write them after fields and lines!

One more thing to add: when data from table A is copied to table B, table B cannot have an auto-increment ID.

If there is an auto-increment ID, do not insert the auto-increment

insert into B (title) select title from A