SQLServerCh07

Chapter 7

How to insert, update,
and delete data

Murach's SQL Server 2012, C7

© 2012, Mike Murach & Associates, Inc.

Slide 1

Objectives
Applied
 Given the specifications for an action query, code the INSERT,
UPDATE, or DELETE statement for doing the action.
 Use the MERGE statement to merge rows from a source table into
a target table.
 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

© 2012, Mike Murach & Associates, Inc.

Slide 2

Objectives (cont.)
 Explain how the MERGE statement works.

Murach's SQL Server 2012, C7

© 2012, Mike Murach & Associates, Inc.

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

© 2012, Mike Murach & Associates, Inc.

Slide 4

Create a complete copy of the Invoices table
SELECT *
INTO InvoiceCopy
FROM Invoices;
(114 row(s) affected)

Create a partial copy of the Invoices table
SELECT *
INTO OldInvoices
FROM Invoices
WHERE InvoiceTotal – PaymentTotal – CreditTotal = 0;
(103 row(s) affected)

Murach's SQL Server 2012, C7

© 2012, Mike Murach & Associates, Inc.

Slide 5

Create a table with summary rows
SELECT VendorID, SUM(InvoiceTotal) AS SumOfInvoices
INTO VendorBalances
FROM Invoices
WHERE InvoiceTotal - PaymentTotal - CreditTotal 0
GROUP BY VendorID;
(7 row(s) affected)

Delete a table
DROP TABLE InvoiceCopy;

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.

Murach's SQL Server 2012, C7

© 2012, Mike Murach & Associates, Inc.

Slide 6

The syntax of the INSERT statement
INSERT [INTO] table_name [(column_list)]
[DEFAULT] VALUES (expression_1 [, expression_2]...)
[, (expression_1 [, expression_2]...)...]

Murach's SQL Server 2012, C7

© 2012, Mike Murach & Associates, Inc.

Slide 7

The values for a new row in the Invoices table
Column
InvoiceID
VendorID
InvoiceNumber
InvoiceDate
InvoiceTotal
PaymentTotal
CreditTotal
TermsID
InvoiceDueDate
PaymentDate

Murach's SQL Server 2012, C7

Value
(Next available unique ID)
97
456789
4/01/2012
8,344.50
0
0
1
4/31/2012
null

© 2012, Mike Murach & Associates, Inc.

Slide 8

Insert the row without using a column list
INSERT INTO InvoiceCopy
VALUES (97, '456789', '2012-04-01', 8344.50, 0, 0, 1,
'2012-04-30', NULL);

Insert the row using a column list
INSERT INTO InvoiceCopy
(VendorID, InvoiceNumber, InvoiceTotal,
PaymentTotal, CreditTotal, TermsID, InvoiceDate,
InvoiceDueDate)
VALUES
(97, '456789', 8344.50, 0, 0, 1, '2012-04-01',
'2012-04-30');

The response from the system
(1 row(s) affected)

Murach's SQL Server 2012, C7

© 2012, Mike Murach & Associates, Inc.

Slide 9

Insert three rows
INSERT INTO InvoiceCopy
VALUES
(95, '111-10098', '2012-04-01', 219.50, 0, 0, 1,
'2012-04-30', NULL),
(102, '109596', '2012-04-01', 22.97, 0, 0, 1,
'2012-04-30', NULL),
(72, '40319', '2012-04-01', 173.38, 0, 0, 1,
'2012-04-30', NULL);

The response from the system
(3 row(s) affected)

Murach's SQL Server 2012, C7

© 2012, Mike Murach & Associates, Inc.

Slide 10

The definition of the ColorSample table
Column
Name
ID
ColorNumber
ColorName

Murach's SQL Server 2012, C7

Data
Type
Int
Int
VarChar

Length
4
4
10

Allow
Identity Nulls
Yes
No
No
No
No
Yes

© 2012, Mike Murach & Associates, Inc.

Default
Value
No
0
No

Slide 11

Six INSERT statements for the ColorSample table
INSERT INTO ColorSample (ColorNumber)
VALUES (606);
INSERT INTO ColorSample (ColorName)
VALUES ('Yellow');
INSERT INTO ColorSample
VALUES (DEFAULT, 'Orange');
INSERT INTO ColorSample
VALUES (808, NULL);
INSERT INTO ColorSample
VALUES (DEFAULT, NULL);
INSERT INTO ColorSample
DEFAULT VALUES;

Murach's SQL Server 2012, C7

© 2012, Mike Murach & Associates, Inc.

Slide 12

The ColorSample table after the rows are inserted

Murach's SQL Server 2012, C7

© 2012, Mike Murach & Associates, Inc.

Slide 13

The syntax of the INSERT statement
for inserting rows selected from another table
INSERT [INTO] table_name [(column_list)]
SELECT column_list
FROM table_source
[WHERE search_condition]

Insert paid invoices into the InvoiceArchive table
INSERT INTO InvoiceArchive
SELECT *
FROM InvoiceCopy
WHERE InvoiceTotal - PaymentTotal - CreditTotal = 0;
(103 row(s) affected)

Murach's SQL Server 2012, C7

© 2012, Mike Murach & Associates, Inc.

Slide 14

The same INSERT statement with a column list
INSERT INTO InvoiceArchive
(InvoiceID, VendorID, InvoiceNumber, InvoiceTotal,
CreditTotal, PaymentTotal, TermsID, InvoiceDate,
InvoiceDueDate)
SELECT
InvoiceID, VendorID, InvoiceNumber, InvoiceTotal,
CreditTotal, PaymentTotal, TermsID, InvoiceDate,
InvoiceDueDate
FROM InvoiceCopy
WHERE InvoiceTotal - PaymentTotal - CreditTotal = 0;
(103 row(s) affected)

Murach's SQL Server 2012, C7

© 2012, Mike Murach & Associates, Inc.

Slide 15

The syntax of the UPDATE statement
UPDATE table_name
SET column_name_1 = expression_1 [, column_name_2 =
expression_2]...
[FROM table_source [[AS] table_alias]
[WHERE search_condition]

Update two columns of a single row
UPDATE InvoiceCopy
SET PaymentDate = '2012-05-21',
PaymentTotal = 19351.18
WHERE InvoiceNumber = '97/522';
(1 row(s) affected)

Murach's SQL Server 2012, C7

© 2012, Mike Murach & Associates, Inc.

Slide 16

Update one column of multiple rows
UPDATE InvoiceCopy
SET TermsID = 1
WHERE VendorID = 95;
(6 row(s) affected)

Update a column using an arithmetic expression
UPDATE InvoiceCopy
SET CreditTotal = CreditTotal + 100
WHERE InvoiceNumber = '97/522';
(1 row(s) affected)

Warning
 If you omit the WHERE clause, all the rows in the table will be
updated.

Murach's SQL Server 2012, C7

© 2012, Mike Murach & Associates, Inc.

Slide 17

A subquery that returns the value assigned
to a column
UPDATE InvoiceCopy
SET CreditTotal = CreditTotal + 100,
InvoiceDueDate = (SELECT MAX(InvoiceDueDate) FROM
InvoiceCopy)
WHERE InvoiceNumber = '97/522';
(1 row(s) affected)

Murach's SQL Server 2012, C7

© 2012, Mike Murach & Associates, Inc.

Slide 18

A subquery used in a search condition
UPDATE InvoiceCopy
SET TermsID = 1
WHERE VendorID =
(SELECT VendorID
FROM VendorCopy
WHERE VendorName = 'Pacific Bell');
(6 row(s) affected)

Another subquery used in a search condition
UPDATE InvoiceCopy
SET TermsID = 1
WHERE VendorID IN
(SELECT VendorID
FROM VendorCopy
WHERE VendorState IN ('CA', 'AZ', 'NV'));
(51 row(s) affected)

Murach's SQL Server 2012, C7

© 2012, Mike Murach & Associates, Inc.

Slide 19

A subquery that identifies the rows available
for update
UPDATE InvoiceCopy
SET CreditTotal = CreditTotal + 100
FROM
(SELECT TOP 10 InvoiceID
FROM InvoiceCopy
WHERE InvoiceTotal - PaymentTotal – CreditTotal
>= 100
ORDER BY InvoiceTotal – PaymentTotal – CreditTotal
DESC)
AS TopInvoices
WHERE InvoiceCopy.InvoiceID = TopInvoices.InvoiceID;
(5 rows(s) affected)

Murach's SQL Server 2012, C7

© 2012, Mike Murach & Associates, Inc.

Slide 20