SQLConfiguration SQLConfiguration


Importing/Exporting Data  «Prev 

Bulk Insert Statment in SQL Server

Bulk Insert Statement consisting of 1) table_name 2) data_file and 3) BATCHSIZE

  1. table_name– The table that you are loading data into
  2. data_file – The file containing the data
  3. [ BATCHSIZE [= batch_size]] – Specifies how many rows to load as one transaction
  4. [[,] CHECK_CONSTRAINTS] – Tells SQL Server to check column constraints when loading data
  5. [[,] CODEPAGE [= 'ACP' | 'OEM' | 'RAW' | 'code_page']] – Specifies the meaning of characters with an ascii value less than 32 or greater than 127
  6. [[,] DATAFILETYPE [= {'char' | 'native'| 'widechar' | 'widenative'}]] – Specifies that the data in the file is all character, native datatypes, unicode characters, or unicode with native datatypes
  7. [[,] FIELDTERMINATOR [= 'field_terminator']] – For character or wide character datatypes, specifies the field separator; this defaults to ‘\t’ a tab
  8. [[,] FIRSTROW [= first_row]] – The first row in the file to load
  9. [[,] FORMATFILE [= 'format_file_path']] – Allows you to specify a file that describes the format of the data file
  10. [[,] KEEPIDENTITY] – Specifies to use the values in the data file for identity columns
  11. [[,] KEEPNULLS] – Specifies that empty columns gets a null assigned rather than the columns default value
  12. [[,] KILOBYTES_PER_BATCH [= kilobytes_per_batch]] – Specifies how many kilobytes will be in a batch
  13. [[,] LASTROW [= last_row]] – The last row to load
  14. [[,] MAXERRORS [= max_errors]] – Specifies to abort the bulk insert after max_errors have been reached; this option defaults to 10
  15. [[,] ORDER ({column [ASC | DESC]} [,...n])] – Specifies the order of the data int the file
  16. [[,] ROWS_PER_BATCH [= rows_per_batch]] – Specifies the number of rows per batch
  17. [[,] ROWTERMINATOR [= 'row_terminator']] – Specifies the row terminator when load char or widechar data; this defaults to‘\r’ a carriage return
  18. [[,] TABLOCK] – Tells SQL Server to lock the whole table