T_ SQL insert data

In course a ** S1, the use of SQL Server to query and manage data mainly introduces the following data insertion methods:

1. Insert a single row of data using the insert statement

Syntax: insert [into] Table name [column name] values (value, value)

Note: The into keyword can be omitted in the preceding statement, but the column name can be omitted. However, if multiple column names are separated by commas (,), multiple values are also separated by commas. The table name is required.

Example: insert into hamber (ID, name, score) values (1, 'hamber', 100)

Note: (1) the non-null constraint of a column cannot be violated. An entire row of data must be inserted at a time, and a half or several columns of data cannot be inserted.

(2) The number of data values must be the same as the number of columns, and the inserted data must match the data type of each column.

(3) The insert statement cannot insert data for the ID column.

(4) For character columns, it is best to enclose the data in single quotes.

(5) Although you can leave the column name unspecified, it is best to complete the column name.

(6) If a column is not empty during table design, the column must be assigned a value.

(7) Each inserted column of data must comply with the column's check Constraints

(8) how to use the default value of a column after setting the default value of a column and specifying the column name?

Assign values. Is to use the default keyword. Note: This method can only be used in the insert into values () statement.

.

2. There are three methods to insert multiple rows at a time

<1> use the insert SELECT statement to add data from an existing table to a new table.

Insert into Table1 (name, address, email)

Select sname, saddress, semail

From Table2

Note: The number, sequence, and data type of the columns queried from table 2 must be consistent with that in table 1 to be inserted.

Table 1 must contain three columns: name, address, and email.

And table 1 must be created in advance!

<2> use the select into statement to add data from an existing table to a new table.

Similar to the preceding insert SELECT statement, the Select into statement inserts query data from the table into the new table.

The difference is that the new table is created when the query statement is executed and cannot exist in advance.

Example: Select sname, saddress, semail

Into Table2

From Table1

In the preceding method, how can I insert an ID column when inserting data into a new table?

Because the data in the ID column cannot be specified, we can create an ID column.

Syntax:

Select Identity (data type, Id seed, Id increment) as column name

Into new table

From original table

In this way, you can add an ID column to the new table by combining the preceding syntax with the preceding example.

<3> Merge data with the Union keyword for insertion

The Union statement is used to combine two different data or query results into a new result set.

When multiple rows of data are repeatedly added to a table, you can use the select Union statement to simplify the operation.

Example:

Insert into Table1 (sname, saddress, semail)

Select 'hamber ', 'beijing', 'hamber _ Bao@live.cn' Union

Select 'Li Yanhong ', 'beijing', 'Baidu @. com' Union

Select 'shi Yuzhu ', 'shanghai, China', 'waf @. com'

This effect is similar to the preceding insert SELECT statement, except that multiple rows of data are handwritten, then, the Union keyword is used to merge multiple rows of data.