0

I'm looking for any suggestion for the workaround of 1000 lines per insert limit in SQL Server.

I have MySQL dump script which has few 100 lines, each line is an insert statement which contains approx 7000 rows.

I cannot insert that directly to SQL Server because of the limit of 1000 rows per insert.

Is there any "easy" workaround or only one way is to programmatically identify rows and create insert per each row?

Daniel Hanczyc
  • 182
  • 1
  • 11
  • 1
    Can you give a little more context? As in an example of the sorts of data you're working with. "Problematically identify rows" could mean a lot of things. – tadman Apr 25 '19 at 00:59
  • 1
    There are a few options depending on how creative you want to get. Do you know any programming languages? You could use Python or PowerShell to iterate through the various insert statements and control the batch and transactions programatically that way or you could use SSIS (SQL Server Integration Services) or do something with BULK INSERT and a batch script. My first thought would be to write a program to do this. – DtechNet Apr 25 '19 at 00:59
  • Yes @tadman said it - please provide a sample of the data or some dummy data that represents it accurately. How many columns? – DtechNet Apr 25 '19 at 01:00
  • As a note, if you're using `mysqldump` you can twiddle the [`--max-allowed-packet`](https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_max_allowed_packet) parameter to make your batch inserts smaller, or to turn off `--extended-insert` if that's causing trouble. – tadman Apr 25 '19 at 01:00

2 Answers2

0

To properly answer your question, exactly as it is asked, no there is not a way natively within the SQL interface to overcome the INSERT limitation. You will need to create a programatic solution. I have listed some technoligies in my comment such as Python, PowerShell, and .NET. You could paste together a solution with BCP, BULK INSERT, SSIS, or some other BI tool. Here are some links for C# that talk about bulk insert a large dataset:

Insert 2 million rows into SQL Server quickly

Bulk inserting with SQL Server (C#)

Also there was a similar question asked and the accepted answer here suggests using SQL Server Import Wizard:

Import Wizard - Bulk Insert

DtechNet
  • 162
  • 3
  • 12
0

You can try UNION ALL SELECT trick to do the insert, as it is 7000 rows only and you will not have performance issues.

INSERT INTO Table1 (ValueA, ValueB)
SELECT 1, 2
UNION ALL SELECT 3,4
UNION ALL SELECT 5,6;

In this way, you will not have 1000 rows limit of VALUES clause.

There is one more approach as mentioned in the Stackoverflow post, where you have INSERT INTO TABLE for each INSERT statement.

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58