Insert a large volume of data in the sql server

1

I downloaded a list of cities on the internet. There are more than 5,000 municipalities. I tried a simple Insert and I could not. You have exceeded the maximum number of 1000 records. Then I tried Bulk, but I do not know if I did it right or not, the fact is that it gives error. My bulk looks like this:

BULK INSERT CIDADE 
FROM 'C:\Lixo\Cidade_Brasil.txt'

And my txt is in this format:

(1, 'Afonso Cláudio', 8),
(2, 'Água Doce do Norte', 8),
(3, 'Águia Branca', 8),
(4, 'Alegre', 8),
(5, 'Alfredo Chaves', 8),
(6, 'Alto Rio Novo', 8),
(7, 'Anchieta', 8),
(8, 'Apiacá', 8),
(9, 'Aracruz', 8),
(10, 'Atilio Vivacqua', 8),
(11, 'Baixo Guandu', 8),
(12, 'Barra de São Francisco', 8),
(13, 'Boa Esperança', 8),
......;

And this is the error message:

  

Message 4832, Level 16, State 1, Line 3 Bulk loading: end   unexpected file in the data file. Message 7399, Level 16,   State 1, Line 3 The OLE DB provider "BULK" to the server   linked "(null)" reported an error. The provider did not provide   information about the error. Message 7330, Level 16, State 2, Line 3   Can not fetch a line from the OLE DB provider "BULK" to the   linked server "(null)".

    
asked by anonymous 06.05.2016 / 20:30

1 answer

2

I created the MUNICIPALOS table:

CREATE TABLE MUNICIPIOS (
   id int,
   dscidade varchar(35),
   estado varchar(2))

I have prepared the import file:

  1,Abadia de Goiás,GO;
  2,Abadia dos Dourados,MG;
  3,Abadiânia,GO;
  4,Abaeté,MG;
  5,Abaetetuba,PA;
  6,Abaiara,CE;
  7,Abaíra,BA;
  8,Abaré,BA;
  9,Alvorada d'Oeste,RO;
 10,Abdon Batista,SC;
 11,Abelardo Luz,SC;
 12,Alta Floresta d'Oeste,RO;
 13,Abre-Campo,MG;
 14,Abreu e Lima,PE;   

Note that I have fields with ', but this will not cause problems.

My import script for this file will look like this:

BULK INSERT MUNICIPIOS
FROM 'C:\TEMP\MUNICIPIOS.TXT'
WITH
 ( ROWTERMINATOR = ';',  
   FIELDTERMINATOR = ',',
   LASTROW = 14 )

I specify the record separator, which is the column separator, and how many lines does the import file have.

    
06.05.2016 / 21:32