SQL Server: "Add and delete"

1. Preface 2. Add Data via SSMs 3. Adding data through SQL statements

• 3.1 Adding a single piece of data
• 3.2 Adding more than one data

4. Importing data from other tables

• 4.1 Importing data from other tables in the database
• 4.2 Importing data from an Excel table

1. Preface

Add and delete changes are the operation of the data, where the "increment" corresponding to the SQL statement is "Insert", that is, "insert" meaning.
This article mainly introduces the addition of data, GUI-style additions, as well as the addition of SQL statements, as well as the addition of imports.

2. Adding data through SSMs

2.1 Select Database->ssmstest-> table->dbo.student Right-click dbo.student Select Edit First 200 rows.

2.2 Follow the data to fill in, and save.

2.3 In the same vein, enter the data into the course table and save it.

2.4 In the same vein, enter the data into the SC table and save it.

2.5 At this point, the data has been added to three tables.

3. Adding data through SQL statements3.1 Adding a single piece of data

3.1.1 Select the database->sqltest, click New Query, notice in the upper left box is sqltest, not the words manually select.
3.1.2 Enter the following code in the click New query interface:

Insertinto studentValues' 0001 ',' Zhang San ',18,' Man ',' Faculty of Letters ')Insertinto studentValues' 0002 ',' John Doe ',19,' Woman ',' Faculty of Science ')Insertinto studentValues' 0003 ',' Harry ',20,' Man ',' Faculty of Letters ')Insertinto studentValues' 0004 ',' Zhao Liu ',21st' Woman ',' Faculty of Science ')Insertinto courseValues' 1001 ',' English ',Null3)Insertinto courseValues' 1002 ',' Sports ',Null3)Insertinto courseValues' 1003 ',' Calligraphy ',Null3)InsertInto SCValues' 0001 ',' 1001 ',70gInsertInto SCValues' 0001 ',' 1002 ',80)InsertInto SCValues' 0001 ',' 1003 ',90gInsertinto sc values (  ' 0002 ',  ' 1001 ', 75) insert into SC values ( ' 0002 ',  ' 1002 ', 85) insert into sc values ( ' 0002 ',   ' 1003 ', 95)           

3.1.3 Click Execute.

3.1.4 Select database->sqltest-> table->dbo.student Right-click dbo.student Select Edit First 200 rows.

3.1.5 can see that the data has been added successfully in the student table, and you can see that the data has been added successfully in the other two tables as well.

3.1.6 to this point, the data has been added to three tables.

3.2 Adding more than one data

Add more than one data at a time, just use the Union keyword.

Insertinto student (Sno, sname, Sage, Ssex, sdept)Select' 0001 ',' Zhang San ',18,' Man ',HumanitiesUnionSelect' 0002 ',' John Doe ',19,' Woman ',ScienceUnionSelect' 0003 ',' Harry ',00n' Man ',HumanitiesUnionSelect' 0004 ',' Zhao Liu ',21st' Woman ',ScienceInsertInto course (CNO, CNAME, cpno, Ccredit)Select' 1001 ',' English ',Null3UnionSelect' 1002 ',' Sports ',Null3UnionSelect' 1003 ',' Calligraphy ',Null3InsertInto SC (Sno, CNO, Grade)Select' 0001 ',' 1001 ',90UnionSelect' 0001 ',' 1002 ',80UnionSelect ' 0001 ',  ' 1003 ', 70 unionselect   ' 0002 ',  ' 1001 ', 75 unionselect   ' 0002 ',  ' 1002 ', 85 unionselect   ' 0002 ',  ' 1003 ', 95   

4. Importing data from other tables4.1 importing data from other tables in the database

SQL Server statements insert data from one table into another table, in the following two ways:
4.1.1 First, when the table to be inserted (Student_back) does not exist

--把表student中性别为‘男‘的学生信息插入到表student_back（不存在）中select * into student_back from student where ssex=‘男‘

4.1.2 Second, when the table to be inserted already exists

--把表student中性别为‘女‘的学生信息插入到表student_back（已存在）中insert into student_back(bno, bname, bage, bsex, bdept)select sno, sname, sage, ssex, sdept from studentwhere ssex=‘女‘

Reference Web site: SQL Server inserting data and deleting data

4.2 Importing data from an Excel table

4.2.1 Open the Excel table to see if the first row is a field name or a specific data, my table here is the first row of data.

4.2.2 Select Database->sqltest, right-click Select Task, import data.

4.2.3 Click Next.

4.2.4 data Source Select Excel, file path Select your file path, generally Excel version in more than 2007 Select 2007, pay special attention to the first row contains the column name there, because my excel in the first row is the data, so I want to cancel that √. Then click Next.

4.2.5 If there is an error, go to this URL (http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/ AccessDatabaseEngine.exe) Download and try again after installation.

4.2.6 Choose the authentication method according to your database login mode. Then click Next.

4.2.7 Click Next.

4.2.8 Select the student table at the target. Then click Next.

4.2.9 Click Next.

4.2.10 Click Next.

4.2.10 Click Finish.

4.2.11 here represents the completion of the import.

4.2.12 Select the database->sqltest-> table->dbo.student Right-click dbo.student Select Edit the first 200 rows to see that the data you just completed has been imported successfully.

SQL Server: "Add and delete"