SELECT INTO and INSERT INTO SELECT table replication statements are briefly introduced

  • 2020-05-13 03:43:17
  • OfStack

Insert is a common statement in T-sql, Insert INTO table(field1,field2...) values (value1 value2,...). This form is essential in application development. However, in the process of development and testing, we often encounter situations that require table replication. For example, partial fields of one table1 data are copied to table2, or the whole table1 is copied to table2. At this time, we will use SELECT INTO and INSERT INTO SELECT table replication statements.
1.INSERT INTO SELECT statement
Statement form: Insert into Table2(field1,field2,...) select value1, value2,... from Table1
The target table Table2 is required to exist, and since the target table Table2 already exists, we can insert constants in addition to the fields of the source table Table1. Here's an example:
 
INSERT INTO SELECT Statement copies the table data  
--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 Table2 
( 
a varchar(10), 
c varchar(10), 
d int, 
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED 
( 
a ASC 
) 
) ON [PRIMARY] 
GO 
--2. Creating 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 Table2 
--3.INSERT INTO SELECT Statement copies the table data  
Insert into Table2(a, c, d) select a,c,5 from Table1 
GO 
--4. Displays the updated results  
select * from Table2 
GO 
--5. Delete test table  
drop TABLE Table1 
drop TABLE Table2 

2.SELECT INTO FROM statement
Statement form: SELECT vale1, value2 into Table2 from Table1
Requires that the target table Table2 does not exist because the table Table2 is automatically created at insertion and the data for the fields specified in Table1 is copied to Table2. Here's an example:
SELECT INTO FROM creates the table and copies the table data
 
--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. Creating 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 creation table Table2 And copy the data  
select a,c INTO Table2 from Table1 
GO 
--4. Displays the updated results  
select * from Table2 
GO 
--5. Delete test table  
drop TABLE Table1 
drop TABLE Table2 

Related articles: