Import and export data from a file. in .NET

Create PDF417 in .NET Import and export data from a file.

Set a database to the bulk-logged recovery model to avoid inflating the transaction log. Run the bcp utility. Perform a Bulk Insert task. Import bulk XML data by using the OPENROWSET function. Copy data from one table to another by using the SQL Server 2005 Integration Services (SSIS) Import/Export Wizard.

Lessons in this chapter:

Lesson 1: Preparing to Work with Flat Files. . . . . . . . . . . . . . . . . . . . . . . . . . . . 381 Lesson 2: Running the bcp Utility. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387 Lesson 3: Performing a BULK INSERT Task . . . . . . . . . . . . . . . . . . . . . . . . . . . 393 Lesson 4: Importing Bulk XML Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398 Lesson 5: Using the SSIS Import/Export Wizard . . . . . . . . . . . . . . . . . . . . . . . 402

10

A computer that meets the hardware and software requirements for Microsoft SQL Server 2005. SQL Server 2005 Developer, Workgroup, Standard, or Enterprise Edition installed.

Daren Bieniek My work since the mid-1990s has focused mostly on business intelligence (BI) and data warehousing, so I have loaded a lot of flat files into many databases. In fact, I have loaded hundreds of terabytes of data from flat files (nearly all into SQL Server), and I consider flat files an excellent choice for loading databases large or small. From my experience, here is a quick story about the importance of using the appropriate file formats for data loads. I was working with a client who was bringing in data from several systems, more than 25 GB a week in flat files, and the client suggested that we leave behind the old flat files and move to the newer XML files. The client could not give me any good reasons why he wanted to change, other than saying it was a general industry direction. I protested and told the client that this is not one of XML s strengths and that the company would incur unnecessary overhead. However, the client insisted that we run a test, and I did. First, the client s 25 GB in flat files grew to more than 100 GB as XML files because of XML s tag overhead, so we now needed four times the storage and bringing the file across the network took more than four times as long. Second, while loading from the XML files, processor utilization increased substantially (from the overhead of XML tag parsing because tags now made up more than 75 percent of the files size), and other resources were also more heavily taxed during this time. Additionally, the load time tripled, causing the load to now extend past the maintenance window. Having learned his lesson, the client immediately decided that it was best to stay with the flat files. The moral of this story is that you should use the format that best fits the data you are loading; not switch to the latest format just because it is there.

Before starting the file imports, it is important to review the factors that influence logging behavior and performance of the bulk data loads. You need to consider factors related to the source of the import, the import mechanism you are using, and the destination of the data. You also need to make sure the database you re loading into is set to the Bulk-Logged recovery model.

After this lesson, you will be able to:

List items that affect the logging and performance of bulk operations. Explain the impact of recovery models during bulk loads. Change the recovery model for a database in preparation for a bulk load.