How to run the million-line INSERT script in SSMS?

The table value constructor has limit of maximum number of lines; when this limit is exceeded, error message 10738 is displayed. In the case in question, the information " INSERT statement exceeds the maximum allowed number of 1000 line values ". That is, each command with the INSERT statement can have up to 1000 rows; no more.

According to the aforementioned documentation, to insert more rows than the limit allows, use one of the following methods:

-- código #1
set nocount on;
set IDENTITY_INSERT [dbo].[cidade] on;  

INSERT INTO cidade (id_cidade,descricao,uf,codigo_ibge,ddd) VALUES (1, 'Abacate da Pedreira (Macapá)','AP',1600550,'96');
INSERT INTO cidade (id_cidade,descricao,uf,codigo_ibge,ddd) VALUES (2, 'Abadia (Jandaíra)','BA',2917904,'75');
INSERT INTO cidade (id_cidade,descricao,uf,codigo_ibge,ddd) VALUES (3, 'Abadia de Goiás','GO',5200050,'62');

To do this you need to open the file in the text editor, and replace

• % by% by% by%

• ), by );

• (1 by INSERT INTO cidade (id_cidade,descricao,uf,codigo_ibge,ddd) VALUES (1

• ...
• (2 by INSERT INTO cidade (id_cidade,descricao,uf,codigo_ibge,ddd) VALUES (2

The text editor makes replacements quickly.

For method 2 - Use a derived table , just replace the code snippet

INSERT INTO cidade (id_cidade,descricao,uf,codigo_ibge,ddd) VALUES 

by

INSERT INTO cidade (id_cidade, descricao, uf, codigo_ibge, ddd) 
  SELECT id_cidade, descricao, uf, codigo_ibge, ddd
    from (values 

and add the code snippet

) as T (id_cidade, descricao, uf, codigo_ibge, ddd);

The final code looks like this:

-- código #3
set IDENTITY_INSERT dbo.cidade on; 

INSERT INTO cidade (id_cidade, descricao, uf, codigo_ibge, ddd) 
  SELECT id_cidade, descricao, uf, codigo_ibge, ddd
    from (values 
                 (1, 'Abacate da Pedreira (Macapá)','AP',1600550,'96'),
                 (2, 'Abadia (Jandaíra)','BA',2917904,'75'),
                 (3, 'Abadia de Goiás','GO',5200050,'62'),
                 ...
    ) as T (id_cidade, descricao, uf, codigo_ibge, ddd);

This is probably the simplest change to make to the file that contains the script.

For method 3 - Import bulk data , and using BULK INSERT, you must transform the SQL script into a data file in CSV format. Something like

1, Abacate da Pedreira (Macapá),AP,1600550,96
2, Abadia (Jandaíra),BA,2917904,75
3, Abadia de Goiás,GO,5200050,62

For this you need to open the file in text editor and:

• delete the first 4 lines
• replace (9 with nothing; that is, delete% with%
• replace INSERT INTO cidade (id_cidade,descricao,uf,codigo_ibge,ddd) VALUES (9 with ),
• replace ), with (1
• ...
• replace 1 with (2
• remove 2 end
• replace (9 with nothing; that is, remove apostrophes.

The text editor makes replacements quickly.

One should be aware of the fact that there are localities where there is elision, such as Santa Bárbara d ' Oeste. As in the script the string delimiter is an apostrophe, I assume that in the name of the locales there is no use of elision. But it's good to check it out first.

The import command looks something like

-- código #2 v2
BULK INSERT Cidade
  from 'cidade.csv'
  with (fieldterminator=',');

Create one (or several) CSV file with this data using any tab. From here you can use BULK INSERT .

BULK INSERT tbl
  FROM 'C:\path\data.csv'
  WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  -- delimitador
    ROWTERMINATOR = '\n',   -- indicador para próxima linha
    ERRORFILE = 'C:\path\erro.csv',
    TABLOCK)

A simpler way to generate the CSV is to have this data in Microsoft Excel and export it to that format.

Your file should look like this:

1,"Abacate da Pedreira (Macapá)","AP",1600550,"96"
2,"Curitiba","PR",34534534,"41"

Rows are separated by line breaks ( ROWTERMINATOR ) and fields per comma ( FIELDTERMINATOR ). See Comma-separated-values (CSV) on Wikipedia.