1. Explanation of differences
1. select into from: organize the query data into a new table to save, and the table structure is consistent with the query structure.
select *（Query results） into newtable（New table name）from where （Follow-up conditions）
That is, query the result -> copy an empty table with the same structure -> copy the data.
2. insert into select: add new data in batches to existing tables.
insert into (Prepared table) select *（Or use the structure you want）from Table name where Various conditions
That is, specify a table to insert data—>process and filter the data—> fill in a prepared form.
2. Give examples and explain them in detail
select into from and insert into select are used to copy tables
The main difference between the two is: select into from requires the target table not to exist because it will be automatically created when inserted; insert into select from requires the target table to exist.
- Copy the table structure and its data:
create table table_name_new as select * from table_name_old
- Copy the table structure only:
create table table_name_new as select * from table_name_old where 1=2;
or:
create table table_name_new like table_name_old
- Copy only table data:
If the two table structures are the same:
insert into table_name_new select * from table_name_old
If the two table structures are different:
insert into table_name_new(column1,column2...) select column1,column2... from table_name_old
Notes:
SELECT INTO can only be used to create new tables, while INSERT INTO SELECT can be used to insert data into existing tables.
SELECT INTO will automatically create a new table, and an error will be reported if the new table already exists. And INSERT INTO SELECT does not create tables, but only adds data to existing tables.
When using INSERT INTO SELECT, you can selectively insert data, filter data through the WHERE clause, or sort data using ORDER BY.
When using SELECT INTO, SQL Server automatically creates the columns if the selected column does not exactly match the columns in the new table, but may lose data types and constraints.
Summarize
This is the end of this article about the difference between select into from and insert into select. For more relevant content on the difference between select into from and insert into select, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!