MySQL SELECT INTO and SQL SELECT INTO

Last Update:2016-09-20 Source: Internet

Author: User

Tags mysql in

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

now there is a table for student, I want to copy this table data into a new table for dust, although can be copied with the following statement, always feel uncomfortable, I hope you help me, thank you.

Answer 01:
CREATE TABLE Dust SELECT * from student;//for replication without creating a new table dust
Answer 02:
INSERT INTO dust SELECT * from student;//has created a new table dust case

Now, please use SELECT. Into.. Statement implementation of the above Dongdong, thank you for your support, thanks again!
User reply: The reply was deleted by moderator in 2008-09-29 06:37:03
User reply: SQL Server can be written like that, but MySQL seems to be no, oh, the other floor Lord you said that two kinds of writing, should be the most common AH
User reply: Create TABLE Dust SELECT * from student;
Netizen reply: The upstairs write is my hair inside of ah, I want is SELECT INTO statement of AH?

Please help the heroes, thank you!
User reply: MySQL does not support the SELECT INTO statement directly back up the table structure and data, some methods can be replaced, there are other methods can be processed, summarized as follows:
Method 1:
MySQL does not support:
Select * into New_table_name from Old_table_name;
Alternative methods:
Create table New_table_name (Select * from Old_table_name);

2. Modify the name of the backup table
3. Log in to MySQL
4. Select a database
5. Execute: The path of the source backup table such as: Source D:/ok_db.sql enter.
6. Complete.

MySQL Select into outfile is used to export the specified query data to the following file:

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

2. Export the data from the specified query condition 2005-06-08 in the table to the C packing directory Outfile1.txt 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 in front, (Col_name_or_user_var,...) In the back if you use the attributes to write directly after the table name, this is not correct, be sure to write to fields and lines behind!

Netizen Reply: Quote 5 floor wufongming reply:
MySQL does not support the SELECT INTO statement directly back up the table structure and data, some methods can be replaced, there are other methods can be processed, summarized as follows:
Method 1:
MySQL does not support:
Select * into New_table_name from Old_table_name;
Alternative methods:
Create table New_table_name (Select * from Old_table_name);

Method 2:
1. Back up the table structure and data first
#导出命令-u user name-p password-h host IP address database name Table Name 1 > Export file. sql
mysqldump-uroot-proot-h192.168.0.88 ok_db okt ...

5 Floor friends to the above landlord on the MySQL in the SELECT INTO statement of confusion, in MySQL does not support the SELECT INTO statement directly back up the table structure and data.

I think that method 1 is much more flexible than method two, because the solution proposed by the upstairs friend is to create a new table, and the table field name and structure are the same as the original table, if there is already a new table is created, but the table has no data, and the new table has different field names but the same type, At this point the second method does not apply.

So I think the method a little better, there is room for change, but the 5 floor friends can put the problem so good already very difficult!

Landlord according to their own needs to choose it!
User reply: Yes, the building is mainly in the SQL Server database with the SELECT INTO statement to achieve table data replication is a good choice!
User reply: There is harvest!
Netizen reply: Thanks, younger brother Admire!
Netizen Reply: Quote 5 floor wufongming reply:
MySQL does not support the SELECT INTO statement directly back up the table structure and data, some methods can be replaced, there are other methods can be processed, summarized as follows:
Method 1:
MySQL does not support:
Select * into New_table_name from Old_table_name;
Alternative methods:
Create table New_table_name (Select * from Old_table_name);

Method 2:
1. Back up the table structure and data first
#导出命令-u user name-p password-h host IP address database name Table Name 1 > Export file. sql
Mysqldump-u ...

To add, a table data is copied to table B, B cannot have a self-increment ID

If there is a self-increment ID, the self-increment is not inserted

Insert into B (title) select title from A

MySQL SELECT INTO and SQL select intoTags: mysqlsqltable database sqlserversql Server2008-10-31 15:2370131 People readComments (2)CollectionReportCategory:MySQL (+)

Now there is a table for student, I want to copy this table data into a new table for dust, although can be copied with the following statement, always feel uncomfortable, I hope you help me, thank you.
Answer 01:
CREATE TABLE Dust SELECT * from student;//for replication without creating a new table dust
Answer 02:
INSERT INTO dust SELECT * from student;//has created a new table dust case