How to run the INSERT script with one million lines in SSMS?

Asked

Viewed 8,557 times

4

I’m trying to run a script on SQL Server Management Studio (SSMS) with more than 1 million records and the following error occurs:

Message 10738, Level 15, State 1, Line 1032 The number of line value expressions in the INSERT statement exceeds the maximum allowed number of 1000 line values.

How do I run this script? What is the SQL command?

An excerpt from the script:

SET IDENTITY_INSERT [dbo].[cidade] ON;  
GO  

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

  • Do you have access to the database table where the data used to generate the text file with INSERT is? If necessary, you have how to regenerate the text file?

  • I have only the script shown in the post.

2 answers

4


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

According to the above mentioned documentation, to insert more lines than the limit allows, use one of the following methods:

  1. Create multiple INSERT instructions;
  2. Use a derived table;
  3. Bulk import data using the utility BCP or the instruction BULK INSERT.

To the method 1 - Create multiple INSERT instructions, each line to be included must be in a single command with the INSERT statement. Something like 

-- 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');

For this it is necessary to open the file in text editor, and replace

  • ), for );

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

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

  • ...
  • (9 for
    INSERT INTO cidade (id_cidade,descricao,uf,codigo_ibge,ddd) VALUES (9

The text editor quickly replaces.

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

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

for

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

and add at the end the code snippet 

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

The final code will look 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);

Probably the simplest change to perform in the file containing the script.

To the method 3 - Import the data in bulk, and using BULK INSERT, one should 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 it is necessary to open the file in text editor and:

  • delete the first 4 lines
  • replace ), for nothing; i.e., eliminate ),
  • replace (1 for 1
  • replace (2 for 2
  • ...
  • replace (9 for 9
  • pull out ) final
  • replace ' for nothing; i.e., withdraw apostrophes.

The text editor quickly replaces.

It should be attentive to the fact that there are localities in which there is elisão, as Santa Bárbara d'West. As in the script the string delimiter is apostrophe, I assume that in the name of the localities there is no use of elision. But it is good to check before.

The import command looks something like 

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

  • @Front I added details on how to implement the 3 methods. I suggest you start with method 2, as it seemed to me that you will have less editing work in the script file.

4

Create a (or several) CSV file with this data using any separator. From there you can use the 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 CSV is to take this data in Microsoft Excel and export to this format.

Your file should have a format similar to:

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

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

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.