Database update--Adding data

Whether a database can maintain the correctness and timeliness of information, and to a large extent rely on the update function of the database is weak and real-time. Database updates include inserting, deleting, repairing


(also known as update) three kinds of operations. This chapter explains how to use these actions to effectively update the database.





Add data when you can view the data in a database table in SQL Server in Enterprise Manager, but this does not handle a large number of data inserts, and you need to use an INSERT statement to solve the problem.





11.1.1 INSERT Syntax


database information often needs to change the user needs to add  荩 琁 nsert statement provides this functionality. There are usually two forms of insert statements. One is inserting a record, and the other is the result of inserting a subquery. The latter can insert more than one record at a time.














Note: when inserting varbinary type of data, its tail "0" will be removed.


when inserting varchar or text-type data, the space that follows is removed, and if you insert a string that contains only spaces, you are considered to have inserted a string of zero length.

The
identity column cannot specify data, and this column should be skipped in the values list.


columns with character types, when inserting data, especially when inserting strings that contain characters other than numeric characters, it is best to enclose them in quotation marks, otherwise error prone.

The order of the columns in the
column_list can be different from the order in the table structure, but values must correspond to the columns in the column_list.





11.1.2 Insert Single-line


The following example shows how to insert a single line of data:


Example 11-1: Insert the data into the Order Merchant information table.


Use Pangu


Insert Firms


(firm_id, F_name, F_intro)


values (10070001, ' SQL ', ' the company that makes the database software ')

The results of
operation are as follows:


(1 row (s) affected)


Example 11-2: Insert data into the Order Merchant information table.


Use Pangu


Insert Firms


/* You can omit column_list/
when all columns in the table are specified

values (10070001, ' SQL ', ' the company that makes the database software ', 100700010007, ' 00-12345678 ', 234325, ' Chengdu ')








11.1.3 Insert subquery results


subqueries can be nested in a SELECT statement to construct the condition of a parent query, or to nest in a


INSERT statement to generate the data to be inserted. INSERT statement syntax for inserting a subquery is as follows:


INSERT [into]


{table_name with (<table_hint_limited> [... n])


| View_name


| rowset_function_limited}


{[(column_list)]


subquery}


its function is to insert in bulk and insert the results of the subquery all at once into the specified table.








11.1.4 inserts data with stored procedures


in an INSERT statement, you can get the data you want to insert by executing a stored procedure. The data being inserted is the storage


The result set retrieved by the SELECT statement during the process. The syntax for inserting data using stored procedures is as follows:


INSERT [into]


{table_name with (<table_hint_limited> [... n])


| View_name


| rowset_function_limited}


{[(column_list)]





EXECUTE Procedure


where procedure can be either an existing system stored procedure or a user-defined stored procedure, or


can write stored procedures directly in the INSERT statement.