Import data to SQL Server

A wrapper for a system call to the bcp utility which bulk inserts to SQL Server.

Usage

bcpImport(
  x,
  connectargs,
  table,
  fieldterminator = "\t",
  rowterminator = ifelse(.Platform$OS.type == "windows", "\r\n", "\n"),
  overwrite = FALSE,
  spatialtype = c("geometry", "geography"),
  bcpOptions = list("-b", 1000, "-a", 4096, "-m", 10),
  ...
)

Arguments

x

dataframe object or path to file

connectargs

named list of connection arguments. See makeConnectArgs.

table

Name of the source table when importing from SQL Server. For specifying the schema in the table name see <schema>.<table> and if not specified the default is "dbo".

fieldterminator

character separator for columns

rowterminator

character separator for rows--new lines

overwrite

Whether to overwrite the table if it exists

spatialtype

spatial data type for schema https://docs.microsoft.com/en-us/sql/relational-databases/spatial/spatial-data-types-overview, ignored if x is not an 'sf' object

bcpOptions

list of additional options to pass to the 'bcp' utility. See details.

...

arguments to pass to system2

Value

Output from system2. See ... to redirect output.

Details

If x is a dataframe object, data.table::fwrite is used to write the in memory object to disk in a temporary file that is deleted when the function exits. The fieldterminator and rowterminator are ignored in this case.

If overwrite is TRUE, any existing table of the same name will be deleted and the schema is inferred from DBI::dbCreateTable. To use a customized schema, create the schema before calling the function and use overwrite=FALSE.

If x is a sf object, the geometry column is converted to binary and written to the database before conversion to geometry/geometry data type. The EPSG code is automatically read from the sf object and used as the SRID.

To override the default path to the bcp command line utility, set the bcputility.bcp.path option. To override the default path to the sqlcmd command line utility, set the bcputility.sqlcmd.path option.

The bcpOptions allows the user to include additional arguments for the call to system2. Please refer to https://learn.microsoft.com/en-us/sql/tools/bcp-utility. The default options are set to the defaults for bcp CLI. -b refers to number of rows to write at a time; 10,000 to 50,000 is a starting recommendation. -a refers to size of packets to be sent in bytes. -e refers to the maximum number of errors before failure.