SQL Workshop - Washington University in St. Louis

SQL Workshop

Insert, Update, Delete

Doug Shook

Test Tables

So far we have only read data

When testing code that may change data, it is important to NEVER use the production DB ? Run the statements on test tables before deploying

There are two ways to accomplish this ? Create a copy of the database ? Use the INTO clause

2

SELECT INTO

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)

These statements create new tables

3

Test Tables

Things to watch out for: ? Only column definitions and data are copied ? Primary keys, indexes, default values, etc. are not included in the new table ? If calculated values are used, you must name the column

To delete a table you can use the DROP TABLE statement

4

INSERT

Creates a new row within a table Two methods:

? Column list ? Ordered values Identity columns should be left off ? Generated by SQL Server ? More on these later

5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download