BULK INSERT or OPENROWSET(BULK...)

Question

Reading this doc: Use BULK INSERT or OPENROWSET(BULK...) to import data to SQL Server

Is there any difference between BULK INSERT and OPENROWSET(BULK...)?

I also saw someone recommend using SSIS.

Any suggestions?

Answer 1

Hi @Riley

Is there any difference between BULK INSERT and OPENROWSET(BULK...)?

Besides the syntax difference, there is no difference to me.

Regarding SSIS, the Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and lets the user transform, clean, and modify data as it is moved. Addition of a Data Flow task to a package control flow makes it possible for the package to extract, transform, and load data.

This task has its own design surface like the Control Flow, where you can arrange task-like components called transforms to manipulate data as it flows in a pipeline from the source to a destination. The Data Flow is the heart of SSIS, because it encapsulates all the data transformation aspects of ETL. It can set several data source and destination which is different with Bulk Insert task.

Drag and drop the data flow task and inside the data flow drag and drop flat file source and OLE DB destinations and copy the data. This approach is useful if we want to perform any SSIS transformations.
Use the SSIS Bulk Insert Task. This approach is more powerful compared to the previous one because internally, Bulk Insert Task uses Bulk Copy (BCP) operation (Which is very fast in SQL Server).

More details you may refer:

data-flow-task

bulk-insert-task

Best regards,

Cosmog Hong

Answer 2

OPENROWSET gives you some more flexibility, because you can filter the rows with a WHERE clause, and you can build expressions from the fields in the file as if they were table columns.

On the otther hand, OPENROWSET requires a format file, which you don't need with BULK INSERT for the plain a simple cases where the file can be described with field and row terminators.

To get more information about these constructs and also BCP, a command-line tool for the same task, see this article on my web site: https://www.sommarskog.se/bulkload.html

When it comes to SSIS, it is popular with many people, but I can't really speak to it. Because, while I generally rank myself an SQL Server expert, I have never come as far as learning SSIS.