Ch 7' Working with relational data

1
Ch 7. Working with relational data
2
Transactions

• Group of statements executed as a group. If all
  statements execute successfully, changes are
  committed. If any statement fails, all changes
  are rolled-back.

3
Transaction

• Process of Transaction
• Put data into buffer cache
• Buffer transfer to Transaction Log
• Check point (false)
• Transfer to data file
• Check point (true)

4
Transaction
5
Transactions

• 3 Types of transactions
• AutoCommit
• Implicit
• Explicit

6
Transactions

• AutoCommit Transactions
• All transactions are committed automatically,
  basically everything we have done so far.

7
Transaction

• Create this table
• CREATE TABLE dbo.tblEmployee2
• (
• EmployeeID int IDENTITY(1,1) PRIMARY KEY
  NOT NULL,
• FirstName nchar(50) NULL,
• LastName nchar(50) NULL,
• DOB datetime NULL,
• Sex nchar(10) NULL,
• )

8
Transactions

• Explicit Transaction
• Manually specify when to Start Transaction and
  when to Commit or Rollback transaction

9
Transaction

• Create this stored procedure
• CREATE PROCEDURE sp_BadTransaction
• AS
• INSERT INTO
• tblEmployee
• ( FirstName,LastName,DOB,Sex)
• VALUES
• ( 'test','person','5/1/2000','male')
• INSERT INTO
• tblEmployee
• ( FirstName,LastName,DOB,Sex)
• VALUES
• ( 'test','person','5/1/2000',ThisIsTooLong')

10
Transaction

• Execute the procedure. 1st procedure executed
  and inserted a record into tblEmployee table.
  2nd procedure failed and did not execute.

11
Transaction

• CREATE PROCEDURE dbo.sp_GoodTransaction
• AS
• BEGIN TRY
• BEGIN TRANSACTION
• INSERT INTO tblEmployee
• ( FirstName,LastName,DOB,Sex)
• VALUES
• ( 'test','person','5/1/2000','male')
• INSERT INTO tblEmployee
• ( FirstName,LastName,DOB,Sex)
• VALUES
• ( 'test2','person','1/15/2003',ThisIsTooLong'
  )
• COMMIT TRANSACTION
• END TRY
• BEGIN CATCH
• ROLLBACK TRANSACTION
• END CATCH

12
Transaction

• Execute the procedure. Both statements failed to
  execute. No record was inserted into tblEmployee
  table.

13
Transactions

• Implicit Transaction
• Transaction starts automatically and is kept open
  until a Commit or Rollback was issued.
• Default is set to disabled
• SET IMPLICIT_TRANSACTIONS ON to enable

14
Transactions

• SET IMPLICIT_TRANSACTIONS ON
• go
• INSERT INTO tblEmployee
• ( FirstName,LastName,DOB,Sex)
• VALUES
• ( 'test','person','5/1/2000','male')
• INSERT INTO tblEmployee
• ( FirstName,LastName,DOB,Sex)
• VALUES
• ( 'test2','person','1/15/2003','female')
• rollback transaction

15
Import/Populating Tables

• Ways to import into SQL Server 2005
• Bulk Insert
• BCP utility

16
Import/Populating Tables

• Bulk Insert
• Import data use user-specified format
• Able to specify field and row terminator

17
Import/Populating Tables

• USE SYBEX
• CREATE TABLE AirportCode
• (
• ID int NULL,
• City nvarchar(50) NULL,
• Code nvarchar(10) NULL
• )

18
Import/Populating Tables

• BULK INSERT
• Sybex.dbo.Airportcode
• FROM
• 'c\airportfile.txt'
• WITH
• (
• FIELDTERMINATOR'',
• ROWTERMINATOR'\n'
• )

19
Import/Populating Tables

• BCP utility
• Command-line tool
• Export table-gtfile
• Export query-gtfile
• Import into SQL Server
• Create format file

20
Import/Populating Tables

• BCP utility
• Exercise 7.1 page 249

21
Import/Populating Tables

• Create countries table
• USE SYBEX
• CREATE TABLE countries
• (
• countrycode char(2),
• Countryname varchar(50)
• )

22
Import/Populating Tables

• Start-gtrun-gtcmd , command prompt opens up
• Enter Bcp sybex.dbo.countries in countries.txt
  f countries.fmt T at command prompt to import
  into sybex.dbo.countries table.
• Sqlcmd E Q select from sybex.dbo.countries
  to verify