1 / 25

Module 8 Importing and Exporting Data

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

tanika
Download Presentation

Module 8 Importing and Exporting Data

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 8 Importing and Exporting Data

  2. Module Overview • Transferring Data To/From SQL Server • Importing & Exporting Table Data • Inserting Data in Bulk

  3. 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

  4. 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

  5. Available Tools for Data Transfer C:\> bcp < > Bulk Copy Program (bcp) BULK INSERT OPENROWSET(BULK) Import / Export Wizard XML Bulk Load

  6. 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

  7. 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

  8. 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

  9. Demonstration 1A: Disabling & Enabling Constraints • In this demonstration, you will see how to disable and re-enable a Check constraint

  10. 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

  11. 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

  12. 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

  13. SQL Server Import/Export Wizard Easy to use wizard for creating an SSIS package that performs simple data transfers

  14. 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

  15. 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

  16. 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

  17. Demonstration 3A: Working with bcp • In this demonstration, you will see how to import a file using the bcp utility

  18. 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

  19. Demonstration 3B: Working with BULK INSERT • In this demonstration, you will see how to import a file using the BULK INSERT command

  20. 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

  21. Demonstration 3C: Working with OPENROWSET • In this demonstration, you will see how to import a file using OPENROWSET

  22. 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

  23. 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.

  24. 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?

  25. Module Review and Takeaways • Review Questions • Best Practices

More Related