I’m working with a database migration from MySQL to SQL Server and running into some issues. I have this huge script file that contains INSERT statements with tons of rows - we’re talking thousands or even tens of thousands of records per statement.
The problem is SQL Server has that 1000 row limit per INSERT, and when I try to run these massive statements, I get errors about the query processor running out of resources and not being able to create a query plan.
I need to find a way to automatically split these giant INSERT statements into smaller chunks of maybe 1000 rows each. I tried using regular expressions but couldn’t get it working right. Manual editing would take forever since there are so many statements.
Anyone know of a tool or technique that can help me break these down automatically? I don’t have access to the original data source, so bulk insert isn’t an option for me.
PowerShell’s perfect for this. I run a simple script that finds INSERT INTO patterns and counts VALUES entries. Once it hits my batch size (I stick with 800-900 rows for safety), it closes the statement and starts fresh. Way easier than Python and handles parentheses counting really well. Saved me hours on Oracle to SQL Server migrations.
Had the same nightmare migrating from Oracle to SQL Server. Here’s what saved me: I used SQL Server Management Studio plus a text editor hack. Loaded the huge INSERT file into Notepad++ and used column mode to spot patterns in the VALUES sections. Then I built a simple batch file with FINDSTR commands to find INSERT statements and WMIC to count rows between parentheses. The script auto-generates new INSERT statements every 750 rows and dumps them into separate files. This dodged all the parsing headaches with weird data types and special characters that regex usually chokes on. Took an hour to build but ran my whole dataset overnight without touching it.
Hit this same issue during a PostgreSQL to SQL Server migration. Ended up writing a C# console app that worked great. The trick is treating the SQL file as a stream instead of loading everything into memory. I read through character by character with a simple state machine that tracks when you’re inside parentheses in VALUES clauses. Once it hits 850 rows (found this number through testing), it spits out the batch and starts building the next INSERT. This approach handles escaped quotes and nested data without breaking - unlike those string-based methods that always seem to fail. Performance was solid too - chewed through a 12GB file in under 15 minutes on my laptop. Pro tip: always keep the original column specs in each new INSERT or you’ll get data type mismatches.
Had this exact problem during a legacy migration last year. Skip the regex headache - I wrote a Python script that reads the SQL file line by line and splits INSERT statements into smaller batches. It finds INSERT statements, grabs the VALUES section, counts rows by tracking parentheses, then spits out new statements when it hits your row limit. I used 500 rows instead of 1000 to give SQL Server’s query processor some breathing room. The script doesn’t mess with special characters or nested quotes in your data. Processed my 50GB migration script in about 20 minutes.
Honestly, just use sqlcmd with GO statements between batches. Way simpler than coding - find/replace every 1000th VALUES row with GO and let SQL Server handle it. Takes about 10 minutes in Notepad++ and runs super smooth.