Chapter 7: Managing and Manipulating Data

1
Chapter 7 Managing and Manipulating Data
2
Overview
P243

• Importing and Exporting Data
• Using Distributed Queries to Access External Data
• Using Cursors to Retrieve Data
• Retrieving XML Data

3
Importing and Exporting Data
P244-246

• Using the bcp Utility and the BULK INSERT
  Statement
• BCP command
• bcp pubs..publishers out publishers.txt -c -T
• bcp pubs..publishers2 in publishers.txt -c -T
• BULK INSERT command
• Use Pubs
• BULK INSERT Publishers2
• FROM 'c\publishers.txt'
• WITH (DATAFILETYPE 'CHAR')

4
Importing and Exporting Data (continued)
P248-251

• Using DTS Tools
• DTS Import/Export wizard
• DTS Designer
• DTS and Enterprise Manager
• DTS Package Execution Utilities
• Dtswiz command prompt utility
• Dtsrun command prompt utility
• DTS RUN utility
• DTS Query Designer

5
Using Distributed Queries to Access External Data
P256-260

• Using Linked Servers
• OPENQUERY function
• SELECT
• FROM OPENQUERY (StoreOwners, 'SELECT LastName
  FROM Contacts')
• Using Ad Hoc queries
• SELECT
• FROM OPENROWSET
• ('Microsoft.jet.oledb.4.0', 'C\StoreOwners.mdb'
  'admin' , Contacts)

6
Using Cursors to Retrieve Data
P263-269

• Introduction to Cursors
• Cursor functionalities
• Allowing positioning at specific rows of the
  result set
• Retrieving one row or block of rows from the
  current position in the result set
• Supporting data modifications to the rows at the
  current position in the result set
• Supporting different levels of visibility for
  changes made by other users to the data in the
  result set
• Providing access to the data in a result set for
  Transact-SQL statements in scripts, stored
  procedures, and triggers

7
Three Cursor Implementations
P263

• Transact-SQL Server Cursors
• Used in scripts, stored procedures, and triggers
• Implemented on the server
• API Server Cursors
• Implemented on the server but managed by API
  cursor functions (OLE DB, ODBC, DB-Library)
• Client Cursors
• Entire result set is cached on client and all
  cursor operations are performed against this
  cached set

8
Working with T-SQL Server Cursors
P268

• Declare the cursor
• Populate the cursor
• Retrieve (fetch) the result set
• Optional update or delete a row
• Close the cursor
• Free resources allocated to the cursor

9
Fetching and Scrolling and Locking
P268-269

• Fetch Retrieving a row from a cursor
• First, Next, Prior, Last, Absolute n, Relative n
• Locking depends on isolation level needed
• Optimistic Concurrency fetched rows are not
  locked assuming others will not be changing the
  information allows more concurrency
• Pessimistic Concurrency places locks on the
  fetched rows reduces concurrency

10
Retrieving Data for XML Format
P273-275

• Returning a result set as an XML document
• SELECT FOR XML
• XML Modes
• RAW each row has a generic identifier
• AUTO result sets are nested
• EXPLICIT query writer controls the output format

11
Accessing XML Data using OPENXML
P275-278

• 1) Declare a variable
• 2) Parse the XML data
• sp_xml_preparedocument
• 3) SELECT the data
• 4) Remove the XML document from memory
• sp_xml_removedocument

12
Review

• Importing and Exporting Data
• Using Distributed Queries to Access External Data
• Using Cursors to Retrieve Data
• Retrieving XML Data

13
Lab A Determining the Indexes of a Table
14
Lab Managing and Manipulating Data

• Exercise 1
• Pages 251-254 Importing and Exporting Data
• Exercise 2
• Pages Using Distributed Queries to Access
  External Data
• Exercise 3
• Pages 269-271 Creating a Cursor to Retrieve Data
• Exercise 4
• Pages 279-280 Retrieving XML Data