Insert large amount of data into sql server

Question

I downloaded a list of cities on the internet. There are more than 5,000 municipalities. I tried a simple Insert and could not. Exceeded the maximum number of 1000 records. Then I tried with Bulk, but I do not know if I did right or not, the fact is that it gives error. My Bulk is 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 that’s the error message:

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

Answer 1

I created the MUNICIPIOS table:

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

I 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 which is the record separator, which is the column separator and how many lines the import file has.