Shulou(Shulou.com)05/31 Report--

This article will explain in detail the use of insert select and select into for you. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Insert into (column name) select column name from table name where condition-do not create a table, only copy table data select column name into table name (this table name does not exist) from table name where condition, create a new table, copy only the selected column name field data 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, such as copying part of the data fields of an table1 to table2, or copying the whole table1 to table2, then we have to use SELECT INTO and INSERT INTO SELECT table replication statements.

The form of 1.INSERT INTO SELECT statement is: Insert into Table2 (field1,field2,...) Select value1,value2,... From Table1 requires that the target table Table2 must exist, and since the target table Table2 already exists, we can insert constants in addition to the fields of the source table Table1. The example is as follows: the copy code is as follows:-- 1. Create a test table create TABLE Table1 (a varchar (10), b varchar (10), c varchar (10), CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED (an ASC)) ON [PRIMARY] create TABLE Table2 (a varchar (10), c varchar (10), d int, CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED (an ASC)) ON [PRIMARY] GO-2. Create test data Insert into Table1 values ('Zhao', 'asds','90'') Insert into Table1 values ('Qian', '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 to copy table data Insert into Table2 (aline c, d) select a meme 5 from Table1 GO-4. Display the updated result select * from Table2 GO-- 5. Delete test table drop TABLE Table1 drop TABLE Table2

The 2.SELECT INTO FROM statement is in the form of SELECT vale1, and value2 into Table2 from Table1 requires that the target table Table2 does not exist, because the table Table2 is automatically created on insertion and the data of the fields specified in the Table1 is copied to the Table2. The example is as follows: the copy code is as follows:-- 1. Create a test table create TABLE Table1 (a varchar (10), b varchar (10), c varchar (10), CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED (an ASC)) ON [PRIMARY] GO-2. Create test data Insert into Table1 values ('Zhao', 'asds','90') Insert into Table1 values (' Qian', '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 Table2 and copy data select aline c INTO Table2 from Table1 GO-4. Display the updated result select * from Table2 GO-- 5. Delete test table drop TABLE Table1 drop TABLE Table2

So much for sharing the usage of insert select and select into. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.