How to update/insert data into the table from pipe separated .csv file

Question

I have imported data from .csv file to temp table and it is like

ID          DEPARTMENT      DESIGNATION       LOCATION     REGION       STATE

Test19768   Barclays|Citi Bank|Ireland      developer   NULL    NULL    NULL

and have to update/insert in EMp table like

ID          DEPARTMENT      DESIGNATION   LOCATION        REGION        STATE
Test19768   Barclays        developer       NULL            NULL        NULL
Test19768   Citi Bank|      NULL            NULL            NULL        NULL
Test19768   Ireland         NULL            NULL            NULL        NULL

Need to insert multiple pipe separated value for each user ID. we can't use STRING_SPLIT as we have version 14.

Answer 1

check this

BULK INSERT TempTable
    FROM 'C:\CSVData\your.csv'
    WITH
    (
        FIRSTROW = 2,
        FIELDTERMINATOR = '|',  --CSV field delimiter
        ROWTERMINATOR = '\n',   --Use to shift the control to next row
        ERRORFILE = 'C:\CSVDATA\ErrorRows.csv',
        TABLOCK
    )

Related Post