BULK INSERT with CSV File in SQL Server

Question

I have a .CSV file containing say 10000 rows. These rows need to be inserted/updated into SQL Server database tables.

There might be the case, where couple of rows may present in database that means those need to be updated and fire trigger in log file. If not present in database, those need to be inserted.

One possible solution is that, I can read one by one line, check the entry in database and build insert/update queries accordingly. But this process may take much time to create update/insert queries and execute them in database. Some times my CSV file may have millions of records.

Is there any other faster way to achieve this feature?

Below is the code how I am trying to implement.In this post I am going do bulk import using BULK INSERT statements. We use both BULK INSERT and MERGE statements as this implementation not only used for inserting the records, also for updating records if already exist.

BULK INSERT tablename
FROM 'D:\xmlData\Trialbalelce.txt' --This is CSV file
WITH ( FIELDTERMINATOR =',',rowterminator = '\n',FIRSTROW = 1 )

I definitely think the MERGE will do what you're looking to do. For logging, you can push the output into the log table or land it in a temp table and then have the stored procedure do whatever it needs to do in order to move it in. Are you already using the MERGE or are you saying that you'll know you'll need to? — Vinnie
– Vinnie, Commented Apr 12, 2014 at 5:36
@Vinnie: Thank you, I don't know how to MERGE with BULK INSERT, I tried Merge with dummy database. MERGE INTO dbo.Daily_Sync AS TGT USING (SELECT CompanyName,(SELECT USER_ID FROM users WHERE USER_ID=@id_usr) AS usrid FROM dbo.custCompInfo_Tab ) AS SRC ON TGT.Companyname = SRC.CompanyName WHEN MATCHED THEN UPDATE SET TGT.Companyname=SRC.companyname WHEN NOT MATCHED THEN INSERT(companyname, UserId) VALUES(SRC.CompanyName, SRC.usrid); Please advice me how to MERGE CSV with BULK INSERT for update. — user3525945
– user3525945, Commented Apr 12, 2014 at 7:13

Vinnie · Accepted Answer · 2014-04-12 17:56:00Z

OK, hopefully all this helps. I took what you posted in the comment and turned it into a stored procedure. The scenario is that you have a flat file that has a consistent format which you'll load to the same table each time (dbo.custCompInfo_Tab). That table will be merged into the final destination (dbo.Daily_Sync) matching on CompanyName. I added a few data columns to illustrate the merge further.

SETUP:

CREATE TABLE dbo.Daily_Sync
    (CompanyName VARCHAR(10)
   , UserId INT
   , col1 INT
   , col2 INT
   , col3 INT
    )

CREATE TABLE dbo.custCompInfo_Tab
    (CompanyName VARCHAR(10)
   , col1 INT
   , col2 INT
   , col3 INT
    )

I have two data files to load, TrialBalance.txt and TrialBalance2.txt. They contain the following data:

TrialBalance.txt

abc,1,2,3
def,4,5,6
qwe,7,8,9
asd,10,11,12
zxc,13,14,15

TrialBalance2.txt

abc,1,2,3
def,20,21,22
qwe,7,8,9
xcv,10,11,12
xbv,13,14,15

I created a stored procedure that truncates the staging table, loads the table with the data from the file path passed in and then merges it into the destination.

CREATE PROCEDURE dbo.loadDailyData
    @FullFilePath NVARCHAR(MAX)
AS 
    BEGIN
        DECLARE @sql NVARCHAR(MAX)  
    TRUNCATE TABLE dbo.custCompInfo_Tab

    SET @sql = N'BULK INSERT dbo.custCompInfo_Tab FROM ''' + @FullFilePath
        + ''' WITH ( FIELDTERMINATOR ='','',ROWTERMINATOR = ''\n'',FIRSTROW = 1 )'

    SELECT  @sql

    EXEC sp_executesql @sql

    MERGE INTO dbo.Daily_Sync AS TGT
        USING 
            (SELECT CompanyName
                  , USER_ID() usrid
                  , col1
                  , col2
                  , col3
             FROM   dbo.custCompInfo_Tab
            ) AS SRC
        ON TGT.Companyname = SRC.CompanyName
        WHEN MATCHED 
            THEN UPDATE
                SET     TGT.Companyname = SRC.companyname
                      , TGT.col1 = SRC.col1
                      , TGT.col2 = SRC.col2
                      , TGT.col3 = SRC.col3
        WHEN NOT MATCHED 
            THEN INSERT (companyname
                       , UserId
                       , col1
                       , col2
                       , col3
                        )
                VALUES  (SRC.CompanyName
                       , SRC.usrid
                       , SRC.col1
                       , SRC.col2
                       , SRC.col3
                        ); 

END

There is dynamic sql here that is used to build the string and other than the rowcounts, the BULK INSERT string is all that is returned.

Finally, we can see the tables before and after:

SELECT  *
FROM    dbo.custCompInfo_Tab
SELECT  *
FROM    dbo.Daily_Sync

EXEC dbo.loadDailyData @FullFilePath = 'D:\xmlData\TrialBalance.txt'


SELECT  *
FROM    dbo.custCompInfo_Tab
SELECT  *
FROM    dbo.Daily_Sync

EXEC dbo.loadDailyData @FullFilePath = 'D:\xmlData\TrialBalance2.txt'


SELECT  *
FROM    dbo.custCompInfo_Tab
SELECT  *
FROM    dbo.Daily_Sync

RESULTS:

enter image description here

Looks like you put a lot of work in to this answer. It deserves more votes!

Collectives™ on Stack Overflow

BULK INSERT with CSV File in SQL Server

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related