module 8 importing and exporting data n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Module 8 Importing and Exporting Data PowerPoint Presentation
Download Presentation
Module 8 Importing and Exporting Data

Loading in 2 Seconds...

play fullscreen
1 / 25

Module 8 Importing and Exporting Data - PowerPoint PPT Presentation


  • 127 Views
  • Uploaded on

Module 8 Importing and Exporting Data. Module Overview. Transferring Data To/From SQL Server Importing & Exporting Table Data Inserting Data in Bulk. Lesson 1: Transferring Data To/From SQL Server. Overview of Data Transfer Available Tools for Data Transfer

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Module 8 Importing and Exporting Data' - tanika


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
module 8 importing and exporting data
Module 8

Importing and Exporting Data

module overview
Module Overview
  • Transferring Data To/From SQL Server
  • Importing & Exporting Table Data
  • Inserting Data in Bulk
lesson 1 transferring data to from sql server
Lesson 1: Transferring Data To/From SQL Server
  • Overview of Data Transfer
  • Available Tools for Data Transfer
  • Improving the Performance of Data Transfers
  • Disabling & Rebuilding Indexes
  • Disabling & Enabling Constraints
  • Demonstration 1A: Disabling & Enabling Constraints
overview of data transfer
Overview of Data Transfer
  • Scenarios
  • Copying or moving between servers
  • Exporting query data to a file
  • Importing table data from a file
  • Copying to or from a view
  • Transforming collations

ETL:

Extract Transform Load

available tools for data transfer
Available Tools for Data Transfer

C:\> bcp

< >

Bulk Copy Program (bcp)

BULK INSERT

OPENROWSET(BULK)

Import / Export Wizard

XML Bulk Load

improving the performance of data transfers
Improving the Performance of Data Transfers
  • Disable constraints, indexes, and triggers
    • No need to check constraints as each row is loaded
    • Indexes don’t need to be maintained during import
    • Important to check business requirements before disabling triggers
  • Minimizing locking
    • Consider the use of TABLOCK to speed up the import
  • Minimizing logging
    • Database must be in BULK_LOGGED or SIMPLE model
    • Additional requirements on table structure and locking
  • Minimize data conversions
    • Use native format when transferring data between SQL Servers
disabling rebuilding indexes
Disabling & Rebuilding Indexes
  • Disabling an index
    • Prevents user access to the index
    • Prevents access to the data if it is a clustered index
    • Keeps index definition in metadata
    • Speeds up data import in tables, as the index is not maintained during the import
  • Enabling an index
    • Rebuilds the index entirely
    • Is easy to automate as the metadata is still present
  • Enabling and disabling indexes can be used as an alternative to dropping and recreating indexes
disabling enabling constraints
Disabling & Enabling Constraints
  • Disabling PRIMARY KEY and UNIQUE constraints
    • Is achieved by disabling the associated index
    • Causes associated indexes to be rebuilt when enabled
    • Can cause failures during re-enabling if duplicate values exist
    • Also causes associated foreign key constraints to be disabled
  • Disabling FOREIGN KEY and CHECK constraints
    • Can be performed directly on the constraint
    • Causes existing data to not be verified when re-enabled
  • When you enable a FOREIGN KEY or CHECK constraint, existing data is not verified by default
demonstration 1a disabling enabling constraints
Demonstration 1A: Disabling & Enabling Constraints
  • In this demonstration, you will see how to disable and re-enable a Check constraint
lesson 2 importing exporting table data
Lesson 2: Importing & Exporting Table Data
  • Overview of SQL Server Integration Services
  • Demonstration 2A: Working with SSIS
  • SQL Server Import/Export Wizard
  • Demonstration 2B: Using the Import/Export Wizard
overview of sql server integration services
Overview of SQL Server Integration Services
  • SSIS is a rich framework to develop ETL solutions
  • SSIS packages contain
    • Data Sources and Destinations
    • Control and Data Flows
    • Transformations to be performed
  • SSIS packages can be run using
    • dtsrun command line utility
    • SQL Server Agent jobs
  • SSIS packages developed using
    • SQL Server Business Intelligence Development Studio (BIDS)
    • Import / Export Wizard
demonstration 2a working with ssis
Demonstration 2A: Working with SSIS

In this demonstration you will see how to use SQL Server Integration Services to export data from a table to a flat file

sql server import export wizard
SQL Server Import/Export Wizard

Easy to use wizard for creating an SSIS package that performs simple data transfers

demonstration 2b using the import export wizard
Demonstration 2B: Using the Import/Export Wizard

In this demonstration you will see how to use the Import/Export Wizard to export data to a CSV file

lesson 3 inserting data in bulk
Lesson 3: Inserting Data in Bulk
  • bcp Utility
  • Demonstration 3A: Working with bcp
  • BULK INSERT Statement
  • Demonstration 3B: Working with BULK INSERT
  • OPENROWSET Function
  • Demonstration 3C: Working with OPENROWSET
bcp utility
BCP Utility
  • Is a command line tool to import and export data
  • Uses a format file when transferring between SQL Instances

Creating a format file:

bcpAdv.Sales.Currencyformatnul -T -c –x -f Cur.xml

Exportingdatainto a file:

bcpAdv.Sales.Currency out Cur.dat -T –c

Importing data using a format file:

bcptempdb.Sales.Currency2 in Cur.dat -T -f Cur.xml

demonstration 3a working with bcp
Demonstration 3A: Working with bcp
  • In this demonstration, you will see how to import a file using the bcp utility
bulk insert statement
BULK INSERT Statement
  • Provides options similar to bcp
  • Runs in the SQL Server process
  • Has CHECK_CONSTRAINTS and FIRE_TRIGGERS options
  • Can be executed within a user-defined transaction

BULKINSERTAdventureWorks.Sales.OrderDetail

FROM'f:\orders\neworders.txt'

WITH

(

FIELDTERMINATOR=' |',

ROWTERMINATOR=' |\n'

);

GO

demonstration 3b working with bulk insert
Demonstration 3B: Working with BULK INSERT
  • In this demonstration, you will see how to import a file using the BULK INSERT command
openrowset function
OPENROWSET Function
  • Allows access to remote data by connecting to a remote data source using an OLE-DB provider
  • Offers a built-in BULK provider for bulk imports from files
    • Can be used in a FROM clause with full functionality
    • Can use INSERT .. SELECT to insert data
    • Offers unique table hints to control operation

SELECT*

FROMOPENROWSET(BULK'c:\mssql\export.csv',

FORMATFILE='c:\mssql\format.fmt',

FIRSTROW= 2)AS a;

GO

demonstration 3c working with openrowset
Demonstration 3C: Working with OPENROWSET
  • In this demonstration, you will see how to import a file using OPENROWSET
lab 8 importing and exporting data
Lab 8: Importing and Exporting Data
  • Exercise 1: Import the Excel spreadsheet
  • Exercise 2: Import the CSV file
  • Exercise 3: Create and test an extraction package
  • Challenge Exercise 4: Compare loading performance (Only if time permits)

Logon information

Estimated time: 45minutes

lab scenario
Lab Scenario

Proseware regularly receives updates of currencies and exchange rates from an external provider. One of these files is provided as an Excel spreadsheet, the other file is provided as a comma-delimited text file. You need to import both these files into tables that will be used by the Direct Marketing team within Proseware.

Periodically the Marketing team requires a list of prospects that have not been contacted within the last month. You need to create and test a package that will extract this information to a file for them.

You are concerned about the import performance for the exchange rate file and you are considering disabling constraints and indexes on the exchange rate table during the import process. If you have time, you will test the difference in import performance.

lab review
Lab Review
  • What kind of information needs to be present in a format file?
  • Which tool or command should you use to read an entire XML document into a column in a SQL Server table?
module review and takeaways
Module Review and Takeaways
  • Review Questions
  • Best Practices