Usage and difference between select into from and insert into select

This article introduces: insert is a common statement in T-SQL, but we often encounter situations that require table replication during development, such as copying some fields of Table 1 to table 2, or copy the entire table 1 to table 2. In this case, we need to use the select into and insert into select table copy statements. Select into from and insert into select are both used to copy tables. The main difference between the two is that select into from requires that the target table does not exist because it is automatically created during insertion. Insert into select from requires the target table to exist. The following describes the syntax of the two. Insert into SELECT statement Statement format: : Insert into Table2 (field1, field2,...) Select value1, value2,... from Table1 Note : (1) the target table Table2 must exist and the field, field2... (2) Note that the primary key constraint of Table 2 also exists. If Table 2 has a primary key and is not empty, then field1, field2... the primary key must be included in (3). Note the syntax. Do not add values or mix it with the SQL statement that inserts a data record. Do not write it as: insert into Table2 (field1, field2 ,...) values (select value1, value2 ,... from Table1) (4) because the target table Table2 already exists, We can insert constants in addition to the fields in the source table Table1. Complete instance: SQL code Replication

    --1.创建测试表    create TABLE Table1    (         a varchar(10),         b varchar(10),         c varchar(10),         CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED         (             a ASC         )    ) ON [PRIMARY]     create TABLE Table2    (         a varchar(10),         c varchar(10),         d int,         CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED         (             a ASC         )    ) ON [PRIMARY]    GO    --2.创建测试数据    Insert into Table1 values(‘赵‘,‘asds‘,‘90‘)    Insert into Table1 values(‘钱‘,‘asds‘,‘100‘)    Insert into Table1 values(‘孙‘,‘asds‘,‘80‘)    Insert into Table1 values(‘李‘,‘asds‘,null)    GO    select * from Table2     --3.INSERT INTO SELECT语句复制表数据    Insert into Table2(a, c, d) select a,c,5 from Table1    GO     --4.显示更新后的结果    select * from Table2    GO    --5.删除测试表    drop TABLE Table1    drop TABLE Table2

 

Select into from statement

 

Statement format: Select vale1, value2 into Table2 from Table1 requires that the target table 2 does not exist, because table Table2 is automatically created during insertion, and the specified field data in Table1 is copied to table 2. Complete instance: SQL code Replication

    --1.创建测试表     create TABLE Table1    (         a varchar(10),         b varchar(10),         c varchar(10),         CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED         (             a ASC         )    ) ON [PRIMARY]    GO     --2.创建测试数据     Insert into Table1 values(‘赵‘,‘asds‘,‘90‘)    Insert into Table1 values(‘钱‘,‘asds‘,‘100‘)    Insert into Table1 values(‘孙‘,‘asds‘,‘80‘)    Insert into Table1 values(‘李‘,‘asds‘,null)    GO     --3.SELECT INTO FROM语句创建表Table2并复制数据     select a,c INTO Table2 from Table1    GO     --4.显示更新后的结果     select * from Table2    GO    --5.删除测试表     drop TABLE Table1    drop TABLE Table2

Usage and difference between select into from and insert into select