MS SQL Basics Tutorial: Database updates-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 three actions for inserting, deleting, modifying (also known as updating). This chapter explains how to use these actions to effectively update the database.

When you can view data in a database table in SQL Server in Enterprise Manager, but this does not handle a large amount of data insertion, you need to use the INSERT statement to resolve the problem.

11.1.1 INSERT Syntax

The information of the database often needs to change the user needs to add the data, INSERT statement provides this function. 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 a varbinary type of data, the "0" of its tail is removed.

When you insert a 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.

For columns of 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 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 the operation are as follows:

(1 row (s) affected)

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

Use Pangu

Insert firms

/* 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 criteria for a parent query, or to nest

Insert statement to generate the data to be inserted. The 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}

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

11.1.4 insert 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 stored

The result set that is retrieved by the SELECT statement in the procedure. 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

You can write stored procedures directly in the INSERT statement.

See the full set of "MS SQL Basics Tutorials"