Bulk Insert for Microsoft SQL Server Table
Why should you use Bulk Insert to copy massive volumes of data into a Microsoft SQL table or view? It is because the process is fast, efficient, and secure.
An example will better explain the Bulk Insert task. Suppose your business has million-row product types stored on a mainframe. However, to populate web pages, your e-commerce system uses SQL Server, and every night you have to update the SQL Server table with the master product list located in the mainframe. To make the task of updating the table very easy and quick, you can save the product list in a tab-delimited format and use the Bulk Insert task to copy the data directly to the SQL Server table.
To make sure that the high-speed copying is not disrupted, you should refrain from performing transformations on the data while it is moving from the source file to the table or view.
Considerations before taking up a Bulk Insert task
•Data only from a text file can be transferred into a SQL Server table or view by the Bulk Insert task. For other DBMSs, data from the source to a text file has to be exported first and then the data has to be imported from the text file to the SQL Server.
•The destination for Bulk Insert must be a table or view in a SQL Server database
•A format file can be used in the Bulk Insert task object as it supports both XML and non-XML files.
•A package that contains a Bulk Insert task can only be run by members of the sysadmin with a fixed server role.
A failure in the Bulk Insert task does not automatically roll back batches loaded successfully
Comments
Post a Comment