SoFunction
Updated on 2025-03-02

An example of method to save SQL query results as a new table

Mysql

Copy table structure and data through SQL command

MySQL saves the results of the SQL query as a new table

# Query data from the table old_table_name, where condition is a condition, and then insert the data into a new tablecreate table new_table_name (select * from old_table_name where condition)
  • new_table_nameThe name of the table to be created
  • old_table_nameThe table to be copied
  • *Represents all columns
  • conditionRepresents the conditions after where

Copy table structure and data through mysqldump command

Execute the following command on the machine where mysql is installed

[root@slave opt]# mysqldump -hlocalhost -P3306 -uroot -p --default-character-set=utf8 Database name Table name > Database save file
  • -hThe hostname of the connection
  • -PThe port where the mysql service is located is generally 3306
  • -uusername
  • -p
  • –default-character-setSet the exported encoding format
  • Database nameDatabase table name in mysql
  • Table nameTable name of the database in mysql
  • Database saves filesTo save the data locally

For example: Save the data of the database testdata to the /opt/ file of the server

[root@slave opt]# mysqldump -hlocalhost -P3306 -uroot -p --default-character-set=utf8 testdata> /opt/;

2. The content in the file is the data containing the table testdata creation and content

SQLServer

Copy table structure and data through SQL command

-- Copy the table structure and data
SELECT  * into [schema].[new_table_name] from [schema].[old_table_name]
  • *Represents all columns
  • schemaschema in sqlserver
  • new_table_nameThe name of the table to be created
  • old_table_nameThe table to be copied

For example: copy the structure and data of the table Class_Info in old_school_schema into the table class_info_3 of new_school_schema

SELECT  * into [new_school_schema].[class_info_3] from [old_school_schema].[Class_Info]

Copy the copy table structure through SQL command

-- Copy the table structure
SELECT  * into [new_school_schema].[class_info_4] from [old_school_schema].[Class_Info] where 1=0
  • *Represents all columns
  • schemaschema in sqlserver
  • new_table_nameThe name of the table to be created
  • old_table_nameThe table to be copied
  • 1 = 0

For example: copy the structure and data of the table Class_Info in old_school_schema into the table class_info_4 of new_school_schema

SELECT  * into [new_school_schema].[class_info_3] from [old_school_schema].[Class_Info] where 1 =0

Oracle

Copy table structure and data through SQL command

-- Copy the table structure and data
create table SCHEMA1.new_table_name    as select * from SCHEMA2.old_table_name
  • SCHEMA1Copy the schema in ORACLE to that schema
  • SCHEMA2The schema in ORACLE, the copied schema
  • new_table_nameThe name of the table to be created
  • old_table_nameThe table to be copied
  • *Represents all columns

For example: Copy the structure and data of the table old_table_name in SCHEMA2 into the table new_table_name of SCHEMA1

create table SCHEMA1.new_table_name    as select * from SCHEMA2.old_table_name

Summarize

This is the end of this article about saving SQL query results as a new table. For more related SQL query results to save the content of saving the new table, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!