It is not uncommon to see importing data from other sources and exporting data into other sources as not all data can be entered row-by-row. Generally, either import or export, it can be considered as an ETL operation and some involve with transformation (T) and some do not. If the requirement is just loading a bulk data into SQL Server without transformation, easiest and fastest way of loading data is either using bcp or BULK INSERT. What is the best? Here is a comparison.
Operation | bcp | BULK INSERT |
---|---|---|
Type of utility | Command line utility | T-SQL utility |
Usage | for both import and export | Import only |
Required TSQL knowledge | No, unless queryout is used for writing a query without mentioning the table name. | Required. |
Constraint enforcement | Does not check CHECK and FOREIGN KEY constraints unless CHECK_CONSTRAINTS is used with -h hint. UNIQUE, PRIMARY KEY, and NOT NULL constraints are always enforced. | Does not check CHECK and FOREIGN KEY constraints unless CHECK_CONSTRAINTS is used. UNIQUE, PRIMARY KEY constraints are always enforced. Inserts blank to NOT NULL character type columns if no value is passed to the column. |
Explicit transaction | Not possible | Possible |
Format file | Can generate and use | Cannot generate but can be used. |
Source location | Can be referred a location in client ENV | Can be refereed only a location within the server. |
Performance | Fast | Fastest |
Protocol | TCP/IP Shared Memory Named Pipes |
In memory |
No comments:
Post a Comment