Transcript lesson23
Moving Data Lesson 23 Skills Matrix Moving Data • When populating tables by inserting data, you will discover that data can come from various sources. • One of these sources could be an application where you would use INSERT, UPDATE, and DELETE statements to populate and manipulate the data you store in a SQL Server database. Import Data • You can import data using the following: – BULK INSERT statements – The bcp utility – Data transformations using SSIS – The import/export wizard – Copy database wizard – Detaching/attaching databases – Backup/restore databases – Bulk insert XML data BULK INSERT • A BULK INSERT statement loads a data file into the database using a user-specified format, without forcing the execution of the constraints defined on the destination object. bcp Utility • The bcp utility, a command-line tool, is commonly used for importing and exporting data by performing bulk imports/exports of text data. • The utility allows you to do the following: – You can bulk export data from a table to a file. – You can bulk export data from a query to a file. – You can bulk import data into SQL Server. – You can create format files. SSIS • In SQL Server 2000 a commonly used tool to import and export data was the SQL Server 2000 DTS Wizard. • Starting with SQL Server 2005, SQL Server provides a new extract, transfer, and load (ETL) platform: SQL Server Integration Services (SSIS). • With SSIS you have the ability to import and export data from heterogeneous sources to various destinations. • The toolset provides you with extensive data transformations. SSIS Import and Export Wizard • The SQL Server Import and Export Wizard offers the simplest method to create the Microsoft SQL Server Integration Services package that copies data from a source to a destination. • The wizard can access a variety of data sources, including: – SQL Server – Flat files – Access – Excel – Other OLE DB providers Copying Databases • One of the handiest tools in the SQL Server arsenal is the Copy Database Wizard. Copy Database Wizard Moving Databases • You may detach your database and attach your database in a different location. • You can also move data by doing a backup and a restore to a different location. Detach Task Bulk-Inserting XML Data • You can bulk-insert data to import large amounts of data in SQL Server using T-SQL syntax. • You can accomplish this by using an OPENROWSET function or a BULK INSERT statement. Bulk-Logged Recovery Model • Choosing a Full database recovery model would have a big impact on the transaction log when performing BULK INSERT statements. • To have less impact on the transaction log, you can implement the Bulk-Logged recovery model. • In contrast to the Full recovery model, the BulkLogged model logs bulk operations in a minimal mode. • This allows the Bulk-Logged model to protect against media failures, provide the best performance, and use the least log space. Summary • In this lesson you learned how to work with relational data in terms of importing and exporting data. • An interesting capability of SQL Server is the various methods you can use to bulk-import or even export data to the file system using command-line utilities such as bcp or the BULK INSERT statement. Summary • SSIS is the ETL tool you use to perform advanced data migrations and specify data workflows with custom scripting and transformations. • The power of this tool is that you can use heterogeneous data sources and destinations. Summary for Certification Examination • Be able to run the bcp utility. The bcp utility has several options, including creating a format file and specifying your input or output result based on a table or a query. • It is important to be able to identify the correct syntax to use to perform various bcp statements. Summary for Certification Examination • Know how to import and export data. • You need to have a good understanding of how to import and export data by using BULK INSERT statements or even by using the OPENROWSET function. • You also can use advanced ETL features with SSIS, and you need to be able to identify which tool to use for each purpose.