SQL Server Series: modify data

1. Insert data

SQL provides the INSERT command in the form of 4 and the select/into command as the main method to insert data. The INSERT command is in the following format:

1> insert/values: Insert a row of data, which is usually used to insert data from the user interface.

2> insert/select: Insert a result set, which is usually used to operate multiple rows of data.

3> insert/exec: insert the execution result of a stored procedure for complex data operations.

4> insert default: Insert a new row consisting of the default values of each column. You can use this row to reserve some data rows for future data by pre-filling.

5> select/into: Use the result set of a SELECT statement to create a new table.

1.1 insert a row of data

The simplest and most direct data insertion method is the insert/values method. The syntax format of the insert/values statement is as follows:

 
Insert [Into]Owner.Table [(Columns ,...)] Values(Value ,...)
 

1.2 Insert the result set of the SELECT statement

Using the insert/SELECT statement, you can migrate data in a query result set to a table. The true capability of this method is that you can use the SELECT statement command to obtain data from almost all data sources and flexibly process the data to meet the current needs. This gives the insert/SELECT statement unparalleled flexibility. Because select statements can return a large number of rows, all such insert methods can insert a large number of rows at a time.

The syntax format of the insert/SELECT statement is as follows:

 
Insert [Into]Owner.TableSelectColumnsFromDatasources[Where conditions]
 

1.3 Insert the result set of the stored procedure

The insert/exec method of the insert operation can use the stored procedure to obtain data and insert the data into the table. The advantage of this method is that you can take advantage of all the T-SQL characteristics, insert columns and stored procedure result set columns must correspond one by one.

Basic syntax of the insert/exec command:

Insert [Into]Owner.Table [(Columns)]ExecStoreprocedure Parameters
 

 

A special problem related to the insert operation is to create a primary key value for the newly inserted row. SQL Server provides the ID column and guid for generating the primary key value.

In SQL Server, you can use the following three methods to obtain the Identifier value generated by the system:

@ Identity: this global variable returns the most recent Identifier value generated by SQL Server for any table, connection, and scope. If another insert operation is executed between the time when you perform the insert operation and the time when you check @ identity, the return value of @ identity is not the Identifier value generated by the system for your insert operation, it is the Identifier value generated by the most recent insert operation.

◊ Scope_identity (): can return the batch processing in the current CALLProgramOr the most recent Identifier value generated by the system within the scope of the process. We recommend that you use this method, because this is the safest way to determine the id value you recently generated.

optimize iden_current (table): returns the most recent id value generated by the system for the specified table, but returns only the id value of the specified table, whether or not the insert operation is performed on other tables, this prevents another insert operation hidden in the trigger from affecting the results returned by this function.