Mahen dran Mahen dran - 2 months ago 8
SQL Question

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

My input data in

file looks like this:

1| 2| 3| 4| 5| 6

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?


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)

        INTO #stack_data
        from stackoverflow_data sd
        WHERE = 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)
            FROM #stack_data sd