Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE
Slide 1
Lecture 8
How to insert, update,
and delete data
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE
Slide 2
Objectives
Applied
Given the specifications for an action query, code the INSERT,
UPDATE, or DELETE statement for doing the action.
Create a copy of a table by using the INTO clause of the SELECT
statement.
Knowledge
Describe the three types of action queries.
Explain how to handle null values and default values when coding
INSERT and UPDATE statements.
Explain how the FROM clause is used in an UPDATE or
DELETE statement.
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE
Slide 3
The syntax of the SELECT INTO statement
SELECT select_list
INTO table_name
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_list]
[HAVING search_condition]
[ORDER BY order_by_list]
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE
Slide 4
Create a complete copy of the Employee table
SELECT *
INTO EmployeeCopy
FROM Employee;
Create a partial copy of the Employee table
SELECT *
INTO RetireEmp
FROM Employee
WHERE YEAR(GETDATE())- YEAR(bdate)>60
Murach's SQL Server 2012, C7 Le Thi Tu Kien – FIT - HNUE
Slide 5
Create a table with summary rows
SELECT DNo, SUM(Salary) AS
SumOfSalaries
INTO DepSumSalary
FROM Employee
GROUP BY DNo;
Warnings
When you use the SELECT INTO statement to create a table, only
the column definitions and data are copied.
Definitions of primary keys, foreign keys, indexes, default values,
and so on are not included in the new table.