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 )

Answer 1

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: