首先，您需要导入CSV文件到数据表

然后可以使用SQLBulkCopy插入大容量行

using System;
using System.Data;
using System.Data.SqlClient;

namespace SqlBulkInsertExample
{
    class Program
    {
      static void Main(string[] args)
        {
            DataTable prodSalesData = new DataTable("ProductSalesData");

            // Create Column 1: SaleDate
            DataColumn dateColumn = new DataColumn();
            dateColumn.DataType = Type.GetType("System.DateTime");
            dateColumn.ColumnName = "SaleDate";

            // Create Column 2: ProductName
            DataColumn productNameColumn = new DataColumn();
            productNameColumn.ColumnName = "ProductName";

            // Create Column 3: TotalSales
            DataColumn totalSalesColumn = new DataColumn();
            totalSalesColumn.DataType = Type.GetType("System.Int32");
            totalSalesColumn.ColumnName = "TotalSales";

            // Add the columns to the ProductSalesData DataTable
            prodSalesData.Columns.Add(dateColumn);
            prodSalesData.Columns.Add(productNameColumn);
            prodSalesData.Columns.Add(totalSalesColumn);

            // Let's populate the datatable with our stats.
            // You can add as many rows as you want here!

            // Create a new row
            DataRow dailyProductSalesRow = prodSalesData.NewRow();
            dailyProductSalesRow["SaleDate"] = DateTime.Now.Date;
            dailyProductSalesRow["ProductName"] = "Nike";
            dailyProductSalesRow["TotalSales"] = 10;

            // Add the row to the ProductSalesData DataTable
            prodSalesData.Rows.Add(dailyProductSalesRow);

            // Copy the DataTable to SQL Server using SqlBulkCopy
            using (SqlConnection dbConnection = new SqlConnection("Data Source=ProductHost;Initial Catalog=dbProduct;Integrated Security=SSPI;Connection Timeout=60;Min Pool Size=2;Max Pool Size=20;"))
            {
                dbConnection.Open();
                using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                {
                    s.DestinationTableName = prodSalesData.TableName;

                    foreach (var column in prodSalesData.Columns)
                        s.ColumnMappings.Add(column.ToString(), column.ToString());

                    s.WriteToServer(prodSalesData);
                }
            }
        }
    }
}

Import the file into Excel by first opening excel, then going to DATA, import from TXT File, choose the csv extension which will preserve 0 prefixed values, and save that column as TEXT because excel will drop the leading 0 otherwise (DO NOT double click to open with Excel if you have numeric data in a field starting with a 0 [zero]). Then just save out as a Tab Delimited Text file. When you are importing into excel you get an option to save as GENERAL, TEXT, etc.. choose TEXT so that quotes in the middle of a string in a field like YourCompany,LLC are preserved also...

BULK INSERT dbo.YourTableName
FROM 'C:\Users\Steve\Downloads\yourfiletoIMPORT.txt'
WITH (
FirstRow = 2, (if skipping a header row)
FIELDTERMINATOR = '\t',
ROWTERMINATOR   = '\n'
)

我希望我可以使用FORMAT和Fieldquote功能，但在我的SSMS版本中似乎不支持

基于SQL Server的CSV导入

1) CSV文件数据之间可能有，(逗号)(例如: 描述)，那么我如何才能使导入处理这些数据?

解决方案

如果使用，(逗号)作为分隔符，则无法区分作为字段结束符的逗号和数据中的逗号。我会使用不同的FIELDTERMINATOR，如||。代码看起来就像。这将完美地处理逗号和单斜杠。

2)如果客户端从excel中创建了csv，那么有数据 逗号被括在"…(双引号)[如下所示 那么导入如何处理这个问题呢?

解决方案

如果你使用BULK插入，那么没有办法处理双引号，数据将 用双引号插入行。 在将数据插入表后，您可以将这些双引号替换为"。

update table
set columnhavingdoublequotes = replace(columnhavingdoublequotes,'"','')

3)我们如何跟踪某些行是否有坏数据，哪些导入被跳过? 导入会跳过不可导入的行吗?

解决方案

处理由于无效数据或格式而未加载到表中的行，可以是 使用ERRORFILE属性，指定错误文件名，它将写入行 有错误到错误文件。代码应该像这样。

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

All of the answers here work great if your data is "clean" (no data constraint violations, etc.) and you have access to putting the file on the server. Some of the answers provided here stop at the first error (PK violation, data-loss error, etc.) and give you one error at a time if using SSMS's built in Import Task. If you want to gather all errors at once (in case you want to tell the person that gave you the .csv file to clean up their data), I recommend the following as an answer. This answer also gives you complete flexibility as you are "writing" the SQL yourself.

注意:我将假设您运行的是Windows操作系统，并且能够访问Excel和SSMS。如果没有，我相信你可以调整这个答案来满足你的需求。

Using Excel, open your .csv file. In an empty column you will write a formula that will build individual INSERTstatements like =CONCATENATE("INSERT INTO dbo.MyTable (FirstName, LastName) VALUES ('", A1, "', '", B1,"')", CHAR(10), "GO") where A1 is a cell that has the first name data and A2 has the last name data for example. CHAR(10) adds a newline character to the final result and GO will allow us to run this INSERT and continue to the next even if there are any errors. Highlight the cell with your =CONCATENATION() formula Shift + End to highlight the same column in the rest of your rows In the ribbon > Home > Editing > Fill > Click Down This applies the formula all the way down the sheet so you don't have to copy-paste, drag, etc. down potentially thousands of rows by hand Ctrl + C to copy the formulated SQL INSERT statements Paste into SSMS You will notice Excel, probably unexpectedly, added double quotes around each of your INSERT and GO commands. This is a "feature" (?) of copying multi-line values out of Excel. You can simply find and replace "INSERT and GO" with INSERT and GO respectively to clean that up. Finally you are ready to run your import process After the process completes, check the Messages window for any errors. You can select all the content (Ctrl + A) and copy into Excel and use a column filter to remove any successful messages and you are left with any and all the errors.

这个过程肯定会比这里的其他答案花费更长的时间，但是如果您的数据是“脏的”并且充满SQL违规，您至少可以一次性收集所有错误并将它们发送给提供数据的人(如果您的场景是这样的话)。

也许不是你想要的，但另一种选择是为notepad++使用CSV Lint插件

该插件可以事先验证csv数据，这意味着检查坏数据，如缺少引号、不正确的十进制分隔符、日期时间格式错误等。而不是BULK INSERT，它可以将csv文件转换为SQL插入脚本。

SQL脚本将为每1000条记录的csv行包含INSERT语句，并调整任何datetime和十进制值。该插件自动检测csv中的数据类型，它将包括一个CREATE TABLE部分，其中包含每个列的正确数据类型。

如果有人想使用powershell导入csv

## Install module if not installed, this is a one time install.
Install-Module SqlServer

## Input SQL Server Variables and CSV path
$csvPath = "D:\Orders.csv"
$csvDelimiter = ","
$serverName = "DESKTOP-DOG5T0Q\SQLEXPRESS"
$databaseName = "OrderDetails"
$tableSchema = "dbo"
$tableName = "Orders"

## Truncate Table
Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query "TRUNCATE TABLE $tableSchema.$tableName"

## Import CSV into SQL
Import-Csv -Path $csvPath -header "Id","Country","Price","OrderQuantity" -Delimiter $csvDelimiter | Write-SqlTableData -ServerInstance $serverName -DatabaseName $databaseName -SchemaName $tableSchema -TableName $tableName -Force

导入csv到SQL server(使用SSMS查询或不查询)