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:
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.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