Como posso inserir 10 milhões de registros no menor tempo possível?

Eu tenho um arquivo (que tem 10 milhões de registros) como abaixo:

line1 line2 line3 line4 ....... ...... 10 million lines 

Então basicamente eu quero inserir 10 milhões de registros no database. então eu li o arquivo e o envio para o SQL Server.

Código c #

 System.IO.StreamReader file = new System.IO.StreamReader(@"c:\test.txt"); while((line = file.ReadLine()) != null) { // insertion code goes here //DAL.ExecuteSql("insert into table1 values("+line+")"); } file.Close(); 

mas a inserção demorará muito tempo. Como posso inserir 10 milhões de registros no menor tempo possível usando C #?

Atualização 1:
Inserção em massa:

 BULK INSERT DBNAME.dbo.DATAs FROM 'F:\dt10000000\dt10000000.txt' WITH ( ROWTERMINATOR =' \n' ); 

Minha tabela é como abaixo:

 DATAs ( DatasField VARCHAR(MAX) ) 

mas estou recebendo o seguinte erro:

Msg 4866, nível 16, estado 1, linha 1
O carregamento em massa falhou. A coluna é muito longa no arquivo de dados da linha 1, coluna 1. Verifique se o terminador de campo e o terminador de linha estão especificados corretamente.

Msg 7399, nível 16, estado 1, linha 1
O provedor OLE DB “BULK” para o servidor vinculado “(null)” relatou um erro. O provedor não forneceu nenhuma informação sobre o erro.

Msg 7330, nível 16, estado 2, linha 1
Não é possível buscar uma linha do provedor OLE DB “BULK” para o servidor vinculado “(null)”.

Abaixo do código trabalhado:

 BULK INSERT DBNAME.dbo.DATAs FROM 'F:\dt10000000\dt10000000.txt' WITH ( FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n' ); 

   

    Por favor, não crie um DataTable para carregar via BulkCopy. Essa é uma solução ok para conjuntos menores de dados, mas não há absolutamente nenhuma razão para carregar todos os 10 milhões de linhas na memory antes de chamar o database.

    Sua melhor aposta (fora do BCP / BULK INSERT / OPENROWSET(BULK...) ) é transmitir o conteúdo do arquivo para o database via um Table-Valued Parameter (TVP). Usando um TVP você pode abrir o arquivo, ler uma linha e enviar uma linha até terminar, e depois fechar o arquivo. Este método tem uma pegada de memory de apenas uma única linha. Eu escrevi um artigo, Streaming Data Into SQL Server 2008 de um aplicativo , que tem um exemplo deste mesmo cenário.

    Uma visão geral simplista da estrutura é a seguinte. Estou assumindo a mesma tabela de importação e nome de campo, conforme mostrado na pergunta acima.

    Objetos de database requeridos:

     -- First: You need a User-Defined Table Type CREATE TYPE ImportStructure AS TABLE (Field VARCHAR(MAX)); GO -- Second: Use the UDTT as an input param to an import proc. -- Hence "Tabled-Valued Parameter" (TVP) CREATE PROCEDURE dbo.ImportData ( @ImportTable dbo.ImportStructure READONLY ) AS SET NOCOUNT ON; -- maybe clear out the table first? TRUNCATE TABLE dbo.DATAs; INSERT INTO dbo.DATAs (DatasField) SELECT Field FROM @ImportTable; GO 

    C # app código para fazer uso dos objects SQL acima está abaixo. Observe como, em vez de preencher um object (por exemplo, DataTable) e, em seguida, executar o Stored Procedure, neste método, é a execução do Stored Procedure que inicia a leitura do conteúdo do arquivo. O parâmetro de input do Stored Proc não é uma variável; é o valor de retorno de um método, GetFileContents . Esse método é chamado quando o SqlCommand chama ExecuteNonQuery , que abre o arquivo, lê uma linha e envia a linha para o SQL Server por meio do IEnumerable e yield return construções e, em seguida, fecha o arquivo. O procedimento armazenado apenas vê uma variável de tabela, @ImportTable, que pode ser acessada assim que os dados começam a aparecer ( observação: os dados persistem por um curto período de tempo, mesmo que não o conteúdo completo, em tempdb ).

     using System.Collections; using System.Data; using System.Data.SqlClient; using System.IO; using Microsoft.SqlServer.Server; private static IEnumerable GetFileContents() { SqlMetaData[] _TvpSchema = new SqlMetaData[] { new SqlMetaData("Field", SqlDbType.VarChar, SqlMetaData.Max) }; SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema); StreamReader _FileReader = null; try { _FileReader = new StreamReader("{filePath}"); // read a row, send a row while (!_FileReader.EndOfStream) { // You shouldn't need to call "_DataRecord = new SqlDataRecord" as // SQL Server already received the row when "yield return" was called. // Unlike BCP and BULK INSERT, you have the option here to create a string // call ReadLine() into the string, do manipulation(s) / validation(s) on // the string, then pass that string into SetString() or discard if invalid. _DataRecord.SetString(0, _FileReader.ReadLine()); yield return _DataRecord; } } finally { _FileReader.Close(); } } 

    O método GetFileContents acima é usado como o valor do parâmetro de input para o procedimento armazenado, conforme mostrado abaixo:

     public static void test() { SqlConnection _Connection = new SqlConnection("{connection string}"); SqlCommand _Command = new SqlCommand("ImportData", _Connection); _Command.CommandType = CommandType.StoredProcedure; SqlParameter _TVParam = new SqlParameter(); _TVParam.ParameterName = "@ImportTable"; _TVParam.TypeName = "dbo.ImportStructure"; _TVParam.SqlDbType = SqlDbType.Structured; _TVParam.Value = GetFileContents(); // return value of the method is streamed data _Command.Parameters.Add(_TVParam); try { _Connection.Open(); _Command.ExecuteNonQuery(); } finally { _Connection.Close(); } return; } 

    Notas Adicionais:

    1. Com alguma modificação, o código C # acima pode ser adaptado para agrupar os dados.
    2. Com pequenas modificações, o código C # acima pode ser adaptado para enviar múltiplos campos (o exemplo mostrado no artigo “Steaming Data …” linkado acima passa em 2 campos).
    3. Você também pode manipular o valor de cada registro na SELECT no proc.
    4. Você também pode filtrar as linhas usando uma condição WHERE no proc.
    5. Você pode acessar a variável da tabela TVP várias vezes; é READONLY, mas não “somente forward”.
    6. Vantagens sobre o SqlBulkCopy :
      1. SqlBulkCopy é INSERT-only, enquanto o uso de um TVP permite que os dados sejam usados ​​de qualquer maneira: você pode chamar MERGE ; você pode DELETE baseado em alguma condição; você pode dividir os dados em várias tabelas; e assim por diante.
      2. Devido a um TVP não ser INSERT-only, você não precisa de uma tabela de teste separada para despejar os dados.
      3. Você pode obter dados de volta do database chamando ExecuteReader vez de ExecuteNonQuery . Por exemplo, se houvesse um campo IDENTITY na tabela de importação de DATAs , você poderia adicionar uma cláusula OUTPUT ao INSERT para passar de volta INSERTED.[ID] (assumindo que ID é o nome do campo IDENTITY ). Ou você pode passar de volta os resultados de uma consulta completamente diferente, ou ambos, já que vários conjuntos de resultados podem ser enviados e acessados ​​via Reader.NextResult() . Obtendo informações de volta do database não é possível quando se utiliza SqlBulkCopy ainda existem várias perguntas aqui em SO de pessoas que querem fazer exatamente isso (pelo menos no que diz respeito aos valores IDENTITY recém-criados).
      4. Para obter mais informações sobre por que às vezes é mais rápido para o processo geral, mesmo que seja um pouco mais lento para obter os dados do disco no SQL Server, consulte este whitepaper da equipe de consultoria ao cliente do SQL Server: Maximizar o rendimento com TVP

    Em C #, a melhor solução é deixar o SqlBulkCopy ler o arquivo. Para fazer isso, você precisa passar um IDataReader direto para o método SqlBulkCopy.WriteToServer . Aqui está um exemplo: http://www.codeproject.com/Articles/228332/IDataReader-implementation-plus-SqlBulkCopy

    A melhor maneira é uma mistura entre a sua primeira solução e a segunda, crie DataTable e, no loop, adicione linhas a ela e use o BulkCopy para fazer o upload para o database em uma conexão. Use isso para obter ajuda na cópia em massa

    Uma outra coisa a prestar atenção é que a cópia em massa é uma operação muito sensível que quase todo erro anulará a cópia, tal se você declarar o nome da coluna no dataTable como “texto” e no DB seu “Text” ele lançará uma exceção , boa sorte.

    Se você deseja inserir 10 milhões de registros no menor tempo para direcionar usando a consulta SQL para fins de teste, você deve usar essa consulta

      CREATE TABLE TestData(ID INT IDENTITY (1,1), CreatedDate DATETIME) GO INSERT INTO TestData(CreatedDate) SELECT GetDate() GO 10000000