document SqlBulk - CLR_Bulk_mt

• CLR_Bulk_mt

This sp allow to manage with flexibility the import of a text file.
Main feauters are:

• manage the date format
• decimal separator
• manage boolean values
• manage null values
• manage encoding types
• cast values as column type
• use the windows impersonification to manage domain grant

Example:

declare @r nvarchar(max),
        @i int

exec @i=dbo.CLR_Bulk_mt 
               'db',--database
               'dbo',--schema
               'table',--table
               'path,--path
               't1.txt',--file
               0,--encoding 1->UTF8, 2-> UTF8 BOM, 3-> UTF7, 4-> UTF32, altro default 1
               0,--fisrt row, where begin to read rows
               '|',--delimitator
               '.',--decimal separator of number in the file
               'c3,c4,c2,c1',--column mapping. not necessary. if not defined uses all table columns. if defined you could use same columns and with a different order.
               '1,2,5,6',--column order. not necessary. if not defined uses the file column order. id defined you could change column place.
               ',,yyyyMMdd,',--you could define wich is the date format for each column (with ',' separator) or one for all (just define on without any sepatator) or nothing to use system setting. (remember! destination column should be a date/datetime type!!!)
               'false',--destination column allows null values? if yes, blank values will be null, if not leaved blank. if you set true and blank are found a exception is throw.
               1,--there is the header in the file?
               0,--buffer value to inmprove performance. you need to try same test to find it!
               0,--do yo uneed to use transaction? if yes, in case of error, nothing will be imported
               0,--trows have always the same number of columns? if no, you can set 1 to manage the case a row has less columns than the others. at the end will go to next row.
               @r output

print @i
print @r

Parameters

Sp returns an int value that indicate the number of imported rows or if less 0 means there was an error.

 db – String

It s the db where there is the table where the file will be imported.
Sp could be in a different db.

 schema – String

It s the table schema.

 table – String

It s the table where the file will be imported.

 path – String

The path of the file.

 file – String

File to import.

 encoding – int

• 1->UTF8
• 2-> UTF8 BOM
• 3-> UTF7
• 4-> UTF32
• Other, default 1

 First_row – int

Then beginning to read the rows.

 delimitator – char

It s the delimitator of text columns.
 decimale – char

It s the decimal separator.
If not defined (‘’) will use the default of system.

 trace – String – (comma list ‘c1,c2,c3,...’)

It s the columns of table to import.
Sp check that:
- columns exists in the table;
- extract the columns informations (type, nullable,..)

If not defined (‘’) will import in the text sequence and manage columns as String nullable.

 positions – String – (comma list of int values ‘1,2,4,9,...’)

Map the previous definition of columns with the position of text columns.
If not defined (‘’) will import in the text sequence.

 options – String – (comma list ‘,,yyyyMMdd,ddMMyy,...’ or single vale ‘yyyyMMdd’)

It s the date format.
If the column it s “date/datetime/smalldatetime” sp try to cast it with the defined format.
If not defined (‘’) will use the default system settings.

 allow_blank – String - (comma list ‘false,,true,...’ or single vale ‘true’)

Manage the blank or null values.
- True:
if text value is blank (‘’,’ ‘) and column is nullable then will import “NULL” otherwise will use the minimal value allowed by that type (‘1900-01-01’ date, 0 number).
- False:
do nothing.

 Heading – bool

• True:
  file has header;
• False:
  file hasn t header.

 buffer_row – int

Sp use a buffer to performe the import.
0 use the default value.
To find the best value it s necessary to try with some values because it depends by hardware of server.

 trans– bool

• True:
  run the import in transaction with rollback in case of error or commit id all fine.
• False:
  no transaction.

 Use_min_rows – bool

• True:
  If the text has some rows with less columns of the definition will continue to the next.
• False:
  if a row has less columns will return an error.

 error – String (ouput)

Returns the error.