Transcript BULK INSERT [[`database`.][`owner`].]`table_name`

Today’s Agenda
 Chapter 7
 Review for Midterm
Data Transfer Tools




DTS (Data Transformation Services)
BCP (Bulk Copy Program)
BULK INSERT command
Other commands like INSERT,
SELECT INTO AND UPDATE (will
discuss these after the midterm)
What is DTS?
 Data Transformation Services (DTS)
allows you to import, export, or
transform data in a single process.
 The definition of this process can be
saved in an object called a package.
 Each package defines a workflow that
includes one or more tasks such as
executing an SQL statement
 Steps define the sequence in which
the task objects are executed.
DTS compliant data sources
 dBase III, dBase
IV, and dBase5
 Microsoft Access
 Microsoft Data Link
 Microsoft Excel
3.0, Excel 4.0,
Excel 5.0, and
Excel 97-2000
 Microsoft ODBC
drivers for Oracle,
SQL Server and
other ODBC data
sources
 Microsoft OLE DB
provider for Oracle,
SQL Server,
Internet Publishing,
and OLAP
Services.
 Microsoft Visual
FoxPro
 Paradox 3.x,
Paradox 4.x, and
Paradox 5.x
 ASCII text files
DTS Terminology
 Uses a DTS Package which includes
–
–
–
–
Connection Types - Table 7-2
DTS Tasks - Table 7-3
DTS Transformations - Table 7-4
DTS Workflow - Table 7-5
 DTS Package can be stored in many
formats
– Table 7-6
DTS Creation Tools
 DTS Package can be created using
the DTS Import/Export wizard, DTS
Designer or using code
 DTS Import/Export Wizard - easiest
way to create a new package
– Can be launched from EM
– Can also be launched from Start button
 DTS Designer - Use this one to edit
the package and make modifications
Data source and destination
Copy option
Source and destination tables
Column Mappings
Transformations
Run/save options
Summary
Class Exercises




Do the following:
Page 219
Page 220
For the Source use my SQL Server INSTRUCTOR
 For the Destination use your SQL
Server instead of SelfPacedCPU
 Break after you are done
DTS Packages
DTS Designer
 The DTS Designer provides:
– A transaction-oriented workflow engine
that allows you to specify a complex
sequence of operations to be performed
as part of a DTS package.
– Integrated data movement. The DTS
Designer uses a 100 percent OLE DB
architecture
– The ability to save DTS packages to the
Microsoft Repository, SQL Server, or files
DTS Designer - workflow
Connection Objects






dBase 5
HTML file
Paradox 5.x
Microsoft Excel 97-2000
Microsoft Access
Microsoft OLE DB provider for SQL
Server, Oracle etc.
 Microsoft Data Link
 Text file
Task Objects









ActiveX Script
Execute Process
Execute SQL
Bulk Insert
Data Driven Query
Execute Package
Send Mail
Execute Process
Transform Data
Executing a DTS package
 After a DTS package has been defined it
can be executed using:
 The dtsrun command prompt utility.
 SQL Server Agent to run a scheduled job
that executes the package. (More in Ch 13)
 The DTS Designer user interface.
 The DTS Export and Import Wizard user
interfaces.
 The Execute method of a DTS Package
object in a custom application
Other data transfer options
 Bulk copy program (bcp) - Copies data
between SQL Server and a data file in
a user-specified format.
 BULK INSERT (SQL command)
Copies a data file into a database
table or view in a user-specified
format.
 INSERT, UPDATE AND SELECT
INTO (SQL commands)
Bcp syntax - lots of switches
bcp [[database_name.]owner.]table_name|
view_name|"query"
in|out|queryout|format datafile
[-m maxerrors] [-f formatfile] [-e errorfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n] [-c] [-w] [-N] [-V (60|65|70)] [-6]
[-q] [-C code_page]
[-t field_terminator] [-r row_terminator]
[-i input_file] [-o output_file]
[-a packet_size]
[-S server_name[\instance_name]]
[-U login_id] [-P password]
[-T] [-v] [-R] [-k] [-E] [-h "hint"]
Bcp data export
 Swiches can be - or /
 Tables, views, or queries can be
exported.
 Table columns cannot be skipped.
 Timestamp values can be copied to
the destination file.
 Computed values can be copied to the
destination file.
BULK INSERT syntax
BULK INSERT [['database'.]['owner'].]'table_name'
FROM data_file
[WITH ([BATCHSIZE[=batchsize]]
[[,]CHECK_CONSTRAINTS]
[[,]CODEPAGE[='ACP'|'OEM'|'RAW|'code_page']
[[,]DATAFILETYPE['char'|'native'|'widechar'|
'widenative']]
[[,]FIELDTERMINATOR[='field_terminator']]
[[,]FIRSTROW[=row]][[,]FIRE_TIRGGERS]
[[,]FORMATFILE[='format_file']]
[[,]KEEPIDENTITY][[,]KEEPNULLS]
[[,]KILOBYTES_PER_BATCH[=KB]]
[[,]LASTROW[=row]][[,]MAX_ERRORS[=max_errors]]
[[,]ORDER(column[ASC|DESC])]
[[,]ROWS_PER_BATCH[=rows]]
[[,]ROWTERMINATOR[='row_terminator']]
[[,]TABLOCK]
)]
Comparison of methods
Which one to choose?
 Use BCP or BULK INSERT when you
have a lot of data to import
 Use BCP or BULK INSERT to import
as part of a batch or script
 Use DTS to move data between
different applications.
 Use DTS when data transformations
are required.
Class Exercises
 Do the following
 Pages 228-233
 If the above exercise does not work for
you (too many steps), create a new
database in SQL Server and import
Northwind database from Access into
SQL Server
 Page 241
 Take a break
Midterm Review









Next week (5:30 - 7:30)
Chapter 1-7
Open Book Open Notes
Straight Scale >90 = A, >80 = B and so on
MCSE Objectives
In class material (slides and handout)
100 Questions (multiple choice)
200 points
If you missed any classes, complete
assignments after midterm
 Any questions?