Transcript Data Transfer

Week 5 – Chap. 5 Data Transfer
• DBAs often must transfer data to and from
text files, Excel spreadsheets, Access,
Oracle or other SQL Server databases
• This process of transferring data is called
migration
• Can be performed using a variety of
methods
SQL Server’s Data Migration Tools
1. Data Transformation Services (DTS)
1. Bulk Copy Program(BCP)
1. Transact-SQL Bulk Insert Statement
1. Transact-SQL Select Into Statement
Data Transformation Services
(DTS)
• Most common method for data transfer
• Easy to use wizard (Start  SQL Server 
Import and Export Data)
• DTS allows changes to data (transformation)
while transferring data
• Transfer of data to : Export
• Transfer of data from : Import
• Can import or export data to / from many
sources (Excel, Oracle, SQL Server, Access)
• Migration steps can be saved by DTS as a
package and rerun later
DTS Transformation of Data
• Mapping Data Types
1. You can specify how data is formatted
between source and destination
2. You can specify how data is modified
between source and destination
• Integrating and Consolidating data
1. You can combine data
2. You can summarize data vertically and
horizontally
DTS Packages
• A set of DTS migration steps can be stored
as a package
• Packages can be saved to Database files or
even in Microsoft Repository Feature
• Packages consist of:
• Connections to Data Sources and
Destinations
• Tasks – Commands to transfer data,
edit data, …
• Workflow – Precedence constraints
for tasks
DTS Package: Connections
Connection is between Source and Destination
Data.
DTS Package: Tasks
• A Task is a unit of work
• A Task could:
• Execute a Transact SQL Statement
• Execute a Script
• Launch an external application
• Copy SQL Server objects
• Execute and retrieve results from a
DTS Package
DTS Package : Workflow
• Workflow links tasks
• Workflow constraints coordinate flow of
control and execution of steps in the DTS
Package by precedence:
Precedence
constraint
Description
On Success
Requires a successful step before
execution
On Completion
Executes automatically after
another step
Executes when there is a failure
On Failure
Workflow Order of Execution
Sequence
Steps must execute in sequence
Parallel
Multiple steps can execute in
parallel to improve performance
Combination Steps use a combination of
sequential and parallel execution
Workflow Steps Priority
By default all threads have the same priority. But
you can change default priority for a step to:
• Idle
• Normal
• High
Package Scheduling, Execution
Ways to execute a DTS Package:
•Use SQL Enterprise Manager
•Use dtsrun command prompt utility
Ways to Schedule a DTS Package:
•Use DTS import/export wizard when saving
to the msdb database
•Use SQL Server Enterprise Manager when
you use dtsrun command prompt utility.
DTS Designer
DTS Designer allows experienced
Database Administrators to import and
export data, transform data and define
complex workflows for heterogeneous data
from multiple sources
DTS Object Transfer
The Copy SQL Server Object task in both the
DTS Import/Export Wizard and in DTS
Designer allows easy transfer of database
objects between SQL Server Databases.
Objects to be transferred can include tables,
views, stored procedures, defaults, rules,
user-defined data types, logins, users, roles
and constraints
You can transfer all objects in a database or
only a subset using this task.
Bulk Copy Program(BCP)
• Bulk Copy Program is performed from O/S
command prompt
• Allows import or export of data from/to a text
file or a binary file
• The data migrated can be a table or a SQL
query result.
• BCP can bypass transaction log files, which
improves speed but can compromise recovery
• Always backup database before and after
BCP operation
BCP (ctd)
• BCP can create a format file to manipulate
data
• BCP is normally an automated process
BCP Export Example
BCP Import Example
BULK INSERT
• A Transact-SQL command that can read a text
file or native SQL Server file into an existing
table or view
• Can also use a format file (as BCP utility can)
• Commands can be saved to a script
• Scripts can be automated by using osql
commands
BULK INSERT (ctd)
• Fastest method of data transfer, because
a session doesn’t have to be started for
each data transfer
• Database should be backed up after this
operation
• Used for copying operations in which
performance is the most important
consideration
• Can’t transform data using this method
BULK INSERT Example
USE pubs
GO
TRUNCATE TABLE authors2
GO
BULK INSERT authors2 from "c:\temp\authors2.csv"
GO
SELECT * FROM authors2
GO
SELECT INTO
• SELECT INTO statement copies data from
one table to another
• Tables can be on the same SQL Server or
linked SQL servers or on different types of
servers using distributed queries
• SELECT INTO operates just like Bulk
Insert except it can’t read from an external
file
• SELECT INTO can also create the
destination table automatically before
copying the data
SELECT INTO - EXAMPLE
USE pubs
GO
DROP TABLE authors2
GO
SELECT * INTO authors2 from
authors
GO
SELECT * FROM authors2
GO
Lab Exercises
1. Export data using Enterprise Manager
2. Import data using Enterprise Manager
3. Truncate table from command line
4. Create Export text file from command line
5. Import data from command line
6. Bulk Insert
7. Select Into
8. Schedule a Package