Insert the rows into the table, one value per insertable column (InsertableColumns), and return how many rows the server inserted. bulkInsertWith (bulkCopy table).

bulkInsert conn "dbo.item" [(1 :: Int32, "one" :: Text), (2, "two")]

bulkInsert with the settings.

bulkInsertWith (bulkCopy "dbo.item") { bulkColumns = NamedColumns ["id", "name"], bulkTableLock = True } conn rows

bulkInsert with rows from a stream and the default settings. Use bulkInsertSWith to choose columns, batching or other options. The streaming and interruption rules of bulkInsertSWith apply.

bulkInsertS conn "dbo.item" rows

bulkInsertWith with the rows from a stream, read as they are sent: a packet's worth at a time, so a stream of any length needs no more memory than that.

The stream runs while the request is being sent, with the connection locked: it must not use this connection (stream the rows from another one; on a MARS connection it may read a stream opened on this one before the load began, but not start a request). If it throws, the load is canceled and the exception is rethrown. Earlier batches stay committed. Cancellation normally leaves the connection usable, but the server can close it for an incomplete large bulk message. A row whose values can't be converted throws EncodeError the same way, naming the row (from 0) and the column. An asynchronous exception (a timeout expiring) while the rows are being sent breaks the connection instead, as an interrupted send does for any request.

With bulkBatchSize, each batch is sent and its response read before the next batch's rows are pulled from the stream. An empty stream sends nothing and returns 0.

The usual source is a query on another connection, which copies a table of any size a packet at a time:

src <- queryS source "SELECT id, name, price FROM dbo.item WHERE price > 1"
n <- bulkInsertS target "dbo.expensive_item" (src :: Stream (Of (Int, Text, Decimal)) IO ())

How a bulk copy is done: the table, which of its columns, the server's bulk load options, and batching. bulkCopy has the defaults for a table.

The defaults for a table: every insertable column, no options, one batch.

Which columns the rows hold.

A column of the ORDER hint.

A column as the bulk load declares it: its name, the wire type the values are sent in (a length, precision and scale, or collation included; sqlTypeTypeInfo gives the type of a declaration such as varchar(20)), whether a NULL may be sent, and whether it is the table's identity column.

The columns the rows of a bulk copy hold (bulkColumns): as given for DescribedColumns; for the others, asked of the server (SELECT TOP 0 ... FROM the table, one round trip). Each column keeps its real type (a varchar(50) column is a TIBigVarChar 50, with its collation), which is what encodeBulkCell validates a value against; the load sends it in a wire type (bulkWireType) the server transcodes from. Throws SqlServerError for a table or column that doesn't exist.

The INSERT BULK statement that announces the load: the table, each column with the type it is sent as, and the options.

The packets of a bulk load message for these columns and rows (each row's values converted already, as encodeBulkCell gives them), as bulkInsertS sends them: COLMETADATA, a ROW per row, and the DONE, framed into packets of the context's size. For tests and benchmarks.

Encode a value for a bulk load into this column: its bytes in the wire type the column is sent as (bulkWireType (bulkColumnType column)), or why the column can't take the value. The column's own type supplies the checks, and the wire type may differ: a varchar column's value is checked against the column's length and code page, then sent as UTF-16 in nvarchar(max) for the server to transcode.

A NULL is sent as NULL to a nullable column and refused for a NOT NULL one. A value's framing is checked as an ordinary parameter's is (valueProblem), so an empty numeric or sql_variant payload can't become a NULL by accident, and a value declared as a fixed char, nchar or binary is padded to its length before conversion, as an RPC parameter is. Where the column's type and its wire type agree, a value already in that type (an Int32 for an int column, a Text that fits an nvarchar column, or a matching RawParam) is sent as it is. A value converted to sql_variant keeps its declared base type, precision, scale, length and collation, with its bytes as they are. Other conversions decode the value and encode it again:

• Integers, Bool, Decimal, Double, Float and Money to any of bit (nonzero is 1), the integer types (fractions truncate toward zero, except Money, which rounds half away from zero), decimal and numeric (rounded to the column's scale, half away from zero, a floating-point value from its exact binary value), float, real, money and smallmoney (rounded to four places); a value outside the column's range is refused. Nonfinite floating sources are refused for bit rather than converted to true.
• Text (and String) to nchar, nvarchar, ntext (as UTF-16), char, varchar, text (validated against the column's UTF-8 or single-byte code page, then sent as UTF-16; a character the page lacks is refused), xml and sql_variant (as nvarchar, in the session's collation). A string longer than the column is refused. SqlXml goes to character and xml columns too. A character column gets its text as it is, without the server's XML canonicalization, and raw XML bytes in an encoding other than UTF-8 or a UTF-16 the client can identify are refused there.
• ByteString to binary, varbinary, image and sql_variant; longer than the column, refused.
• Day, TimeOfDay, LocalTime, UTCTime, DateTimeOffset and ZonedTime to date, time, datetime2, datetimeoffset (at the column's scale, rounded as the ToSqlField instances round), datetime and smalldatetime: a date at midnight, the date or time part of a date-time, a datetimeoffset's local time, a date-time with offset zero.
• UUID to uniqueidentifier only; every value but SqlXml to sql_variant.

Anything else (a Text for an int column, a number for a string column, a CLR type) is refused: convert it in Haskell, or declare the column (DescribedColumns) with the type the value is sent as and let the server convert.

The wire type a column's values are sent as, which may differ from the column's real type (bulkColumnType). Character (char, varchar, text) and ntext columns are sent as nvarchar(max) (UTF-16) and transcoded by the server to the column's own collation on insert (as bcp -w does): correct whatever the collation, where a single-byte value would otherwise be read in the load's default collation, not the column's. image and xml are sent as varbinary(max); XML keeps its byte encoding for the server's parser. Everything else is sent as itself. encodeBulkCell still validates a value against the real type, so a character absent from the column's code page or a value too long is refused on the client, not silently transcoded or truncated by the server.