Mahen dran Mahen dran - 1 month ago 5
SQL Question

How to perform bulk insertion in SQL Server from dat file?

My input data in

.dat
file looks like this:

1| 2| 3| 4| 5| 6
11|22|33|44|55|66


upto 3 lakhs of rows

This dat file contains 3 lakhs of rows

Dat file contains 6 columns i need to add one default column and perform bulk insertion into SQL Server

How can I do it?

Answer

Adding sample execution code for your problem

CREATE TABLE stackoverflow_data (
    no VARCHAR(50)
    ,Name VARCHAR(50)
    ,id VARCHAR(50)
    ,state VARCHAR(50)
    ,address VARCHAR(50)
    ,testdate DATETIME2(3)
);

SELECT  sd.no
        ,sd.Name
        ,sd.id
        ,sd.state
        ,sd.address
        INTO #stack_data
        from stackoverflow_data sd
        WHERE sd.no = 0

BULK INSERT #stack_data FROM '\\test\others\input.dat' WITH (FIRSTROW = 1, ROWTERMINATOR = '\n', FIELDTERMINATOR = '|', ROWS_PER_BATCH = 10000)

insert INTO stackoverflow_data (no, Name, id, state, address, testdate)
    SELECT  sd.no
            ,sd.Name
            ,sd.id
            ,sd.state
            ,sd.address 
            ,'10-Dec-2014'
            FROM #stack_data sd