SQL Bulk Insert

By Admin at 2 Jun 2010, 16:03 PM
  • The Bulk Insert can be used to insert data from a text file or a flat file into a SQL Server Table. When creating SQL Server 2000 ETL solutions, such as importing of text files, it is common for developers to employ DTS packages to achieve results quickly. The downside of this approach is that DTS packages have certain programmability limitations and don't always offer the best performance.

    If we want to accomplish the insert task quickly, then we can choose the BULK INSERT. Since the speed of inserting data in SQL Server is wholly dependent on how many writes occur to the transaction log, the BULK INSERT command can be used to take advantage of minimal logging.

    Bulk Insert Syntax:

    1. BULK INSERT
    2. [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]
    3. FROM'data_file'
    4. [ WITH
    5. (
    6. [ [ , ] BATCHSIZE = batch_size ]
    7. [ [ , ] CHECK_CONSTRAINTS ]
    8. [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
    9. [ [ , ] DATAFILETYPE =
    10. { 'char' | 'native'| 'widechar' | 'widenative' } ]
    11. [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
    12. [ [ , ] FIRSTROW = first_row ]
    13. [ [ , ] FIRE_TRIGGERS ]
    14. [ [ , ] FORMATFILE = 'format_file_path' ]
    15. [ [ , ] KEEPIDENTITY ]
    16. [ [ , ] KEEPNULLS ]
    17. [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
    18. [ [ , ] LASTROW = last_row ]
    19. [ [ , ] MAXERRORS = max_errors ]
    20. [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
    21. [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
    22. [ [ , ] ROWTERMINATOR = 'row_terminator' ]
    23. [ [ , ] TABLOCK ]
    24. [ [ , ] ERRORFILE = 'file_name' ]
    25. )]

    Syntax Explanation:

    database_name

    We can specify the database name, in which the table or view resides that we want to update. The default assumption is the current database.

    table_name

    Specify the name of the table or view which is the target to import the data file into.

    'data_file'

    Specify the full path of the data file. BULK INSERT can import data from a disk (including network disk, removable disk, hard disk, etc.). The full path used must designate a valid path from the server running SQL Server.

    FIELDTERMINATOR ='field_terminator'

    Specify the field terminator to be used for char and widechar data files. The default field terminator is a tab character (\t).

    KEEPIDENTITY

    If we want to keep the identity value or values in the imported data file, we can specify it in this argument.

    KEEPNULLS

    Specify that empty columns should retain a null value during the bulk-import operation, instead of having any default values for the columns inserted.

    ROWTERMINATOR ='row_terminator'

    Specify the row terminator to be used for char and widechar data files. The default row terminator is the newline character (\r\n).

    Bulk Insert without using Format Files:

    Example 1:
    Consider the following source file:

    undefined

    In the above file, the field delimiter is a comma and the row delimiter is a new line character.

    Use the following scripts:

    1. CREATETABLE #Customer(CID INT,CustomerName VARCHAR(200),Address VARCHAR(200))
    2. BULK INSERT #Customer FROM'C:\CustomerDet.txt'
    3. WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')
    4. SELECT * FROM #Customer
    5. DROPTABLE #Customer

    In Example 1, #Customer is the target table and CustomerDet.txt is the source data file. The source file in this case is located in the root of the C drive. The FIELDTERMINATOR argument allows you to specify the delimiter used to discern column values.

    Result:

    undefined

    Example 2

    Consider the following source file:

    undefined

    In the above file, the field delimiter is a tab and the row delimiter is a new line character.

    Use the following scripts:

    1. CREATETABLE #Customer(CID INT,CustomerName VARCHAR(200),Address VARCHAR(200))
    2. BULK INSERT #Customer FROM'C:\Customer.txt'
    3. SELECT * FROM #Customer
    4. DROPTABLE #Customer

    In Example 2, #Customer is the target table and Customer.txt is the source data file. The source file in this case is located in the root of the C drive. There is no need to specify the FIELDTERMINATOR because the default field delimiter is a tab.

    Result:

    undefined


    Bulk Insert using Format Files:


    If the files to import are very large, raw text files that don't include formatting such as column headers, then BULK INSERT with format files is the best solution.

    Example:

    Consider a format file named Cust.fmt (note the file extension .fmt). This format file would be placed in the same file system directory where the import source text files are located.

    Here is a sample of a format file:

    1. 8.0
    2. 3
    3. 1 SQLCHAR 0 0 "\t" 1 CID SQL_Latin1_General_CP1_CI_AS
    4. 2 SQLCHAR 0 0 "\t" 2 CustomerName SQL_Latin1_General_CP1_CI_AS
    5. 3 SQLCHAR 0 0 "\n" 3 Address SQL_Latin1_General_CP1_CI_AS

    Consider the following source file:

    undefined

    A format file allows you to perform BULK INSERTS from the source text file in the following manner:

    1. CREATETABLE #Customer(CID INT,CustomerName VARCHAR(200),Address VARCHAR(200))
    2. BULK INSERT #Customer
    3. FROM'C:\Customer.txt'
    4. WITH (FORMATFILE = 'C:\Cust.fmt')
    5. SELECT * FROM #Customer
    6. DROPTABLE #Customer

    Comments

     

    Post a comment

    Please correct the following: