A wrapper for a system call to the bcp utility which bulk inserts to SQL Server.
bcpImport( x, server, database, table, driver = "SQL Server", maxerrors = 10, batchsize = 1000, packetsize = 4096, regional = FALSE, trustedconnection = TRUE, username, password, fieldterminator = "\t", rowterminator = ifelse(.Platform$OS.type == "windows", "\r\n", "\n"), overwrite = FALSE, spatialtype = c("geometry", "geography"), ... )
x | dataframe object or path to file |
---|---|
server | the instance of SQL Server to which to connect |
database | Specifies the database to connect to |
table | name of the source table when exporting from SQL Server |
driver | name of driver for ODBC connection |
maxerrors | maximum number of errors allowed |
batchsize | number of rows to write at a time; 10,000 to 50,000 is a starting recommendation |
packetsize | size of packets to be sent |
regional | Specifies that currency, date, and time data is bulk copied into SQL Server using the regional format defined for the locale setting of the client computer |
trustedconnection | use integrated security, username and password are not required |
username | login ID |
password | password for login ID |
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 |
... | arguments to pass to system2 |
No return value. Operations from bcp are printed to console; see
...
to redirect output
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.