在SQL Server中使用CSV文件进行大量插入 [英] BULK INSERT with CSV File in SQL Server

问题描述

我有一个 .CSV 文件包含说10000行。这些行需要插入/更新到SQL Server数据库表中。

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.

一个可能的解决方案是，我可以一行读取，检查数据库中的条目并构建insert /相应地更新查询。但是这个过程可能需要很多时间来创建更新/插入查询并在数据库中执行它们。有时候，我的 CSV 文件可能有数百万条记录。

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?

下面是我想要实现的代码。在这篇文章中，我将使用BULK INSERT语句进行批量导入。我们使用 BULK INSERT 和 MERGE 语句，因为此实现不仅用于插入记录，还用于更新记录if已经存在。

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 )

推荐答案

确定，希望所有这些有帮助。我把你在评论中发布的，并把它变成一个存储过程。方案是，您有一个具有一致格式的平面文件，您将每次将其加载到同一个表（ dbo.custCompInfo_Tab ）。该表将合并到 CompanyName 匹配的最终目标（ dbo.Daily_Sync ）。我添加了几个数据列，以进一步说明合并。

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
    )

我有两个要加载的数据文件，TrialBalance.txt和TrialBalance2。文本。它们包含以下数据：

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

这里有一个动态的sql，除了rowcounts， BULK INSERT 字符串是所有返回的。

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

结果：

这篇关于在SQL Server中使用CSV文件进行大量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！