Sql server® 2012 t-sql fundamentals

 

CHAPTER 8

 

Data Modification

 

 287

When you’re done, run the following code for cleanup.

IF OBJECT_ID('dbo.OrderDetails', 'U') IS NOT NULL DROP TABLE dbo.OrderDetails; 

IF OBJECT_ID('dbo.ProductsAudit', 'U') IS NOT NULL DROP TABLE dbo.ProductsAudit; 

IF OBJECT_ID('dbo.Products', 'U') IS NOT NULL DROP TABLE dbo.Products; 

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders; 

IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE dbo.Customers; 

IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1; 

IF OBJECT_ID('dbo.Sequences', 'U') IS NOT NULL DROP TABLE dbo.Sequences; 

IF OBJECT_ID('dbo.CustomersStage', 'U') IS NOT NULL DROP TABLE dbo.CustomersStage;

Conclusion

In this chapter, I covered various aspects of data modification. I described inserting, updating, delet-

ing, and merging data. I also discussed modifying data through table expressions, using TOP (and 

indirectly OFFSET-FETCH) with modification statements, and returning modified rows using the 

OUTPUT clause.

Exercises

This section provides exercises so you can practice the subjects discussed in this chapter. The database 

assumed in the exercise is TSQL2012.

1

Run the following code to create the dbo.Customers table in the TSQL2012 database.

USE TSQL2012; 

 

IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE dbo.Customers; 

 

CREATE TABLE dbo.Customers 

( 

  custid      INT          NOT NULL PRIMARY KEY, 

  companyname NVARCHAR(40) NOT NULL, 

  country     NVARCHAR(15) NOT NULL, 

  region      NVARCHAR(15) NULL, 

  city        NVARCHAR(15) NOT NULL   

);

www.it-ebooks.info

---