Bài giảng Cơ sở dữ liệu nâng cao - Chapter 4: Transferring data and using service broker

Bài giảng Cơ sở dữ liệu nâng cao - Chapter 4: Transferring data and using service broker. Nội dung chính trong chương này gồm có: Introduction, performing a bulk load, performing a bulk insert task, importing bulk XML data, overview of SQL server integration services, using the import/export wizard, service broker.
Nội dung trích xuất từ tài liệu:
Bài giảng Cơ sở dữ liệu nâng cao - Chapter 4: Transferring data and using service brokerCHAPTER 4Transferring Data and using Service Broker Agenda• Introduction• Performing a Bulk Load• Performing a Bulk Insert task• Importing Bulk XML data• Overview of SQL Server Integration Services• Using the Import/Export Wizard• Service Broker Introduction• A common task when working with a database is importing data from other sources.• One of the most frequently used methods of transferring data.• There are different methods you can use to efficiently import files into SQL Server • bulk copy program (bcp) • the BULK INSERT Transact-SQL command • the OPENROWSET Transact-SQL function • the SQL Server Integration Services (SSIS) Import/Export Wizard Performing bcp utility• Because bcp is a program, you do not execute it from within a query window or batch but rather from the command line.• It is an external program, which means it runs outside of the SQL Server process• bcp to bulk copy data either into or out of SQL Server. Performing bcp utility• Two limitations of bcp: • bcp has limited data-transformation capabilities. If the data that you are loading needs to go through complex transforms or validations, bcp is not the correct tool to use. • bcp has limited error handling capabilities. bcp might know that an error occurred while loading a given row, but it has limited reaction options Performing bcp utilitybcp {dbtable | query} {in | out | queryout | format} datafile [option] Option Description -S Instance name -T -T Indicates that you wish BCP to connect using a trusted connection -U login name when not using –T -P the password associated with the –U login name -c Use a character data file type -w Use a Unicode data file type -t Field terminator -r Row terminator Performing bcp utility• bcp Command-Line Syntaxbcp {dbtable | query} {in | out | queryout | format} datafile [option,..n]Example about the simplest commandbcp FileImportDB..Exam out c:FileImportPracticeExamImportFile.txt -T –wbcp FileImportDB..Exam in c:FileImportPracticeExamImportFile.txt -T -w BULK INSERT statement• Closely mimics the BCP command line utility• Two of the biggest differences between bcp and BULK INSERT BULK INSERT bcp can only import data can either import or export data Run inside the SQL Server Executed from the command process space and is executed line and runs outside of the from a query window or query SQL Server process space, batch BULK INSERT statementBULK INSERT {dbtable} FROM {datafile} [WITH (option)] BULK INSERT statement• Example: BULK INSERT FileImportDB..Exam FROM c:FileImportPracticeExamImportFile.txt Importing Bulk XML data• SQL Server provides several options for importing XML documents. You can use the OPENROWSET Transact-SQL function to read data, including XML data, from a file.• SQL Server also offers many ways to transfer data: • OPENXML and XML stored procedures • the XML data type’s nodes() method • the SQLXML middle-tier API to load XML data as relational data• In this lesson, we will focus to OPENROWSET to read data from a file. Importing Bulk XML data• OPENROWSET function can be used in any standard SQL statement as a table reference.• There are many uses of the OPENROWSET function, including using the function as a target of an INSERT, UPDATE, or DELETE query. Importing• Example: INSERT INTO Documents(XmlCol) SELECT * FROM OPENROWSET( BULK c:XMLDocsXMLDoc9.txt, SINGLE_BLOB) AS x• This statement bulk imports the contents of the ‘c:XMLDocsXMLDoc9.txt’ file as a SINGLE_BLOB and inserts that BLOB into the XmlCol of a single row in the Documents table.• The SINGLE_BLOB format tells the OPENROWSET function to treat the entire file as a single unit, rather than parsing it in some way.Overview of SQL Server Integration Services• The SQL 2000 platform used Data Transformation Services (DTS)  2005 and 2008 platforms use Integration Services.• SSIS builds on the basic principles of DTS, but expands its capabilities to include additional, easier-to-manage, features.• Integration Services provides enterprise-level data integration and workflow solutions that have as their goal the extraction, transformation, and loading (ETL) of data from various sources to various destinations.• SSIS includes a wide range of tools and wizards to assist in the creation of the workflow and data flow activities that you need to manage in these complex data-movement solutions. Overview of SQL Server Integration Services• SSIS has 4 components: • Integration Services itself • Integration Services object model • Inte ...