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:
SELECT
no proc. SqlBulkCopy
:
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. 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). 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