Select into and insert into select table copy statements: select into and insert into select

Select into and insert into select table copy statements

Insert is a common statement in T-SQL. insert into table (field1, field2,...) values (value1, value2 ,...)ProgramDevelopment is indispensable. However, during the development and testing processes, we often encounter situations where table replication is required, such as copying some of the data fields of Table 1 to table 2 or copying the entire table 1 to table 2, at this time, we will useSelect into and insert into select table copy statements.

1.Insert into SELECT statement

Statement format:Insert into Table2 (field1, field2,...) Select value1, value2,... from Table1

The target table Table2 must exist. Because the target table Table2 already exists, We can insert constants in addition to the fields in the source table Table1. Example:

-- 1. Create a test table
Create   Table Table1
(
A Varchar ( 10 ),
B Varchar ( 10 ),
C Varchar ( 10 ),
Constraint   [ Pk_table1 ]   Primary   Key   Clustered
(
A ASC
)
) On   [ Primary ]

Create   Table Table 2
(
A Varchar ( 10 ),
C Varchar ( 10 ),
D Int ,
Constraint   [ Pk_table2 ]   Primary   Key   Clustered
(
A ASC
)
) On   [ Primary ]
Go
-- 2. Create Test Data
Insert   Into Table1 Values ( ' Zhao ' , ' ASDs ' , ' 90 ' )
Insert   Into Table1 Values ( ' Money ' , ' ASDs ' , ' 100 ' )
Insert   Into Table1 Values ( ' Sun ' , ' ASDs ' , ' 80 ' )
Insert   Into Table1 Values ( ' Li ' , ' ASDs ' , Null )
Go
Select   *   From Table 2

--3. insert into SELECT statement to copy table data
Insert IntoTable2 (A, C, D)SelectA, C,5 FromTable1
Go

--4. display the updated results
Select * FromTable 2
Go
--5. Delete the test table
Drop TableTable1
Drop TableTable 2

2.Select into from statement

Statement format:Select vale1, value2 into Table2 from Table1

The target table Table2 does not exist,Because table 2 is automatically created during insertion, and the specified field data in table 1 is copied to table 2.. Example:

-- 1. Create a test table
Create   Table Table1
(
A Varchar ( 10 ),
B Varchar ( 10 ),
C Varchar ( 10 ),
Constraint   [ Pk_table1 ]   Primary   Key   Clustered
(
A ASC
)
) On   [ Primary ]
Go

-- 2. Create Test Data
Insert   Into Table1 Values ( ' Zhao ' , ' ASDs ' , ' 90 ' )
Insert   Into Table1 Values ( ' Money ' , ' ASDs ' , ' 100 ' )
Insert   Into Table1 Values ( ' Sun ' , ' ASDs ' , ' 80 ' )
Insert   Into Table1 Values ( ' Li ' , ' ASDs ' , Null )
Go

--3. Select into from statement to Create Table 2 and copy data
SelectA, cIntoTable 2FromTable1
Go

--4. display the updated results
Select * FromTable 2
Go
--5. Delete the test table
Drop TableTable1
Drop TableTable 2

Insert is a common statement in T-SQL. insert into table (field1, field2 ,...) values (value1, value2 ,...) this form is essential for application development. However, during the development and testing processes, we often encounter situations where table replication is required, such as copying some of the data fields of Table 1 to table 2 or copying the entire table 1 to table 2, at this time, we will useSelect into and insert into select table copy statements.

1.Insert into SELECT statement

Statement format:Insert into Table2 (field1, field2,...) Select value1, value2,... from Table1

The target table Table2 must exist. Because the target table Table2 already exists, We can insert constants in addition to the fields in the source table Table1. Example:

-- 1. Create a test table
Create   Table Table1
(
A Varchar ( 10 ),
B Varchar ( 10 ),
C Varchar ( 10 ),
Constraint   [ Pk_table1 ]   Primary   Key   Clustered
(
A ASC
)
) On   [ Primary ]

Create   Table Table 2
(
A Varchar ( 10 ),
C Varchar ( 10 ),
D Int ,
Constraint   [ Pk_table2 ]   Primary   Key   Clustered
(
A ASC
)
) On   [ Primary ]
Go
-- 2. Create Test Data
Insert   Into Table1 Values ( ' Zhao ' , ' ASDs ' , ' 90 ' )
Insert   Into Table1 Values ( ' Money ' , ' ASDs ' , ' 100 ' )
Insert   Into Table1 Values ( ' Sun ' , ' ASDs ' , ' 80 ' )
Insert   Into Table1 Values ( ' Li ' , ' ASDs ' , Null )
Go
Select   *   From Table 2

--3. insert into SELECT statement to copy table data
Insert IntoTable2 (A, C, D)SelectA, C,5 FromTable1
Go

--4. display the updated results
Select * FromTable 2
Go
--5. Delete the test table
Drop TableTable1
Drop TableTable 2

2.Select into from statement

Statement format:Select vale1, value2 into Table2 from Table1

The target table Table2 does not exist,Because table 2 is automatically created during insertion, and the specified field data in table 1 is copied to table 2.. Example:

-- 1. Create a test table
Create   Table Table1
(
A Varchar ( 10 ),
B Varchar ( 10 ),
C Varchar ( 10 ),
Constraint   [ Pk_table1 ]   Primary   Key   Clustered
(
A ASC
)
) On   [ Primary ]
Go

-- 2. Create Test Data
Insert   Into Table1 Values ( ' Zhao ' , ' ASDs ' , ' 90 ' )
Insert   Into Table1 Values ( ' Money ' , ' ASDs ' , ' 100 ' )
Insert   Into Table1 Values ( ' Sun ' , ' ASDs ' , ' 80 ' )
Insert   Into Table1 Values ( ' Li ' , ' ASDs ' , Null )
Go

--3. Select into from statement to Create Table 2 and copy data
SelectA, cIntoTable 2FromTable1
Go

--4. display the updated results
Select * FromTable 2
Go
--5. Delete the test table
Drop TableTable1
Drop TableTable 2