Table copy statements insert into and select

In complex business logic, data in one table is often copied to another table;

SQL provides me with the most common method 2:

First, insert

Second, select

Here is an example of an instance:

 
----- Orders table ------------------ create table mdprices (ID int identity () not null primary key, goods int not null, area int not null, guideprice decimal ), createtime datetime default getdate () ----------------------------------- history tables --------------- create table md_historyprices (idint identity () not null primary key, goods int not null, area int not null, price decimal (18,4), createtime datetime) ---------- insert into -------- insert into md_historyprices (goods, area, price, createtime) select goods, area, guideprice, createtime from mdprices ---------- query -------------- select * From md_historyprices ----------------------------------
 

In the second case, select

Syntax select * into Table 1 from table 2

Table 1 cannot exist in our DB. Table 1 is created when the meaning of select into is executed, and does not need to be created in advance;

Select Identity (smallint, 1, 1) as ID, goods, area, guideprice, createtime into historyprice from mdprices

 

The Identity () function is used to edit the auto-increment number from 1 in the historyprice table. The seed value is 1;

Syntax: Identity (data_type, seed, increment) as column_nmae

Only use the SELECT statement with the into Table clause to insert the ID column into the new table, but not the identity attribute used with create table.