Recently I was given the task of executing hundreds of prepared sql files some containing multi-row insert statements numbering in the tens of thousands.
So I encountered two problems with this:
- MSSQL Multi-row insert statements actually have some limits i.e. a maximum of 1000 rows can be inserted.
- I didn’t know which files had more or less than a thousand insert statements.
Luckily all the new rows were going into the same table and I knew that each file had the same flat/unformatted tsql structure.
This meant I could write a batch script to combine the files and then split the tsql insert statements into batches of 1000.
The batch script below is a little long winded so here are the main sections.
- Combine Files
- Remove unwanted lines of text i.e. blank lines or insert statement sections.
- Ensure every parentheses is followed by a comma
- Inject the “insert into table” statement and GO every 1000 lines.
- Remove double quotes
This was a somewhat specific case but hopefully you’ll be able to pick the batch script below apart for yourself and get some use out of it. But if there’s one little snippet of code I’d like to draw your attention to it is this little gem.
TYPE *.sql > CombinedScript.sql
Type that into a text file and save it as combine.bat, place the file in the folder with all your sql scripts and the OS will combine them all for you as CombinedScript.sql.
Here’s the rest of the script followed by some sample data showing the shape and structure of the original sql files.
(I’d like to thank the hilite.me website for creating this awesome online utility for creating HTML highlighted code that can just be dropped into a blog, as shown below. No more gist for me!)
@ECHO OFF
::VARIABLES
SET FileToUse="CombinedScript.sql"
SET FileToDelete="Query.sql"
SET FirstLine="Insert into [LoadProfiles] (Profile,Type,ProfileDate,ProfileValue,Active,Created,CreatedBy) Values"
SET BatchSplit="GO "
SET TextLineToAdd=%BatchSplit%%FirstLine%
::SET "TextLineToAdd=%TextLineToAdd:"=%"
::Combine SQL FILES
ECHO Combining Files . . .
ECHO Please Wait
ECHO.
TYPE *.sql > %FileToUse%
ECHO Files Combined
ECHO.
ECHO Removing unwanted lines of text . . .
ECHO Please Wait
ECHO.
::REF 1
::REMOVE THE LINES WITH INSERT AND VALUES FROM FILE
findstr /v "Insert Values" %FileToUse% > Temp.sql
::REF 2
::REMOVE BLANK ROWS FROM Temp.sql
findstr /v /r /c:"^$" /c:"^\ *$" Temp.sql >> CleanedFile.sql
::DELETE Temp.sql
IF EXIST Temp.sql del /F Temp.sql
ECHO Lines Removed
ECHO.
::REF 3
::REPLACE ) WITH ), TO ENSURE EVERY PARENTHESES IS FOLLOWED BY A COMMA
ECHO Adding Comma to each Parentheses missing a Comma . . .
ECHO Please Wait
ECHO.
setlocal enableextensions disabledelayedexpansion
set "search=)"
set "replace=),"
set "textFile=CleanedFile.sql"
for /f "delims=" %%i in ('type "%textFile%" ^& break ^> "%textFile%" ') do (
set "line=%%i"
setlocal enabledelayedexpansion
set "line=!line:%search%=%replace%!"
>>PreparedFile.sql echo(!line!
endlocal
)
::DELETE CleanedFile.sql
IF EXIST CleanedFile.sql del /F CleanedFile.sql
::REF 3
::REPLACE ),, WITH ), TO ENSURE EVERY PARENTHESES IS FOLLOWED BY ONLY ONE COMMA
setlocal enableextensions disabledelayedexpansion
set "search=),,"
set "replace=),"
set "textFile=PreparedFile.sql"
for /f "delims=" %%i in ('type "%textFile%" ^& break ^> "%textFile%" ') do (
set "line=%%i"
setlocal enabledelayedexpansion
set "line=!line:%search%=%replace%!"
>>CleanedFile.sql echo(!line!
endlocal
)
ECHO Commas Added
ECHO.
ECHO Creating Query file . . .
ECHO Please Wait
ECHO.
::DELETE PreparedFile.sql
IF EXIST PreparedFile.sql del /F PreparedFile.sql
::Try to delete the file only if it exists
IF EXIST %FileToDelete% del /F %FileToDelete%
::REM If the file wasn't deleted for some reason, stop and error
IF EXIST %FileToDelete% exit 1
TYPE NUL > %FileToDelete%
::REF 4
SETLOCAL
SET count=0
SET injectevery=1000
FOR /f "delims=" %%Z IN ('type CleanedFile.sql^|findstr /n "^"') DO (
SET /a count+=1
SET line=%%Z
SETLOCAL ENABLEDELAYEDEXPANSION
ECHO(!line:*:=! >> Query.sql
IF !count!==%injectevery% ECHO.%TextLineToAdd% >> Query.sql
ENDLOCAL
SET /a count=count %% %injectevery%
)
::REF 3
::REPLACE " WITH NOTHING
ECHO Adding Comma to each Parentheses missing a Comma . . .
ECHO Please Wait
ECHO.
setlocal enableextensions disabledelayedexpansion
set "search=""
set "replace= "
set "textFile=Query.sql"
for /f "delims=" %%i in ('type "%textFile%" ^& break ^> "%textFile%" ') do (
set "line=%%i"
setlocal enabledelayedexpansion
set "line=!line:%search%=%replace%!"
>>Script.sql echo(!line!
endlocal
)
::DELETE CleanedFile.sql
IF EXIST CleanedFile.sql del /F CleanedFile.sql
PAUSE
::REF 1: http://stackoverflow.com/questions/418916/delete-certain-lines-in-a-txt-file-via-a-batch-file
::CREDIT GOES TO: http://stackoverflow.com/users/14138/rick
::REF 2: http://www.computing.net/answers/programming/delete-blank-line-from-a-txt-file-using-batch/25575.html
::CREDIT GOES TO: http://www.computing.net/userinfo/150780
::REF 3: http://stackoverflow.com/questions/23075953/batch-script-to-find-and-replace-a-string-in-text-file-without-creating-an-extra
::CREDIT GOES TO: http://stackoverflow.com/users/2861476/mc-nd
::REF 4: FOR INSERT NEW TEXT LINE: http://stackoverflow.com/questions/15859128/looking-for-batch-file-to-insert-new-lines-into-text-file
::CREDIT GOES TO: http://stackoverflow.com/users/2128947/magoo
/*CHANGE DATABASE_NAME*/
USE [DATABASE_NAME]
GO
/****** Object: Table [dbo].[LoadProfiles] Script Date: 06/12/2015 15:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LoadProfiles](
[ProfileID] [int] IDENTITY(1,1) NOT NULL,
[Profile] [int] NULL,
[Type] [varchar](8) NULL,
[ProfileDate] [date] NULL,
[ProfileValue] [decimal](12, 10) NULL,
[Active] [bit] NULL,
[Created] [datetime] NULL,
[CreatedBy] [int] NULL,
PRIMARY KEY CLUSTERED
(
[LoadProfileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Insert into [dbo].[LoadProfiles] (Profile,Type,ProfileDate,ProfileValue,Active,Created,CreatedBy)
Values
(1,'24H','2012/01/01',0.003348,1,getutcdate(),333),
(1,'24H','2012/01/02',0.003392,1,getutcdate(),333),
(1,'24H','2012/01/03',0.003278,1,getutcdate(),333),
(1,'24H','2012/01/04',0.003252,1,getutcdate(),333),
(1,'24H','2012/01/05',0.003203,1,getutcdate(),333)
Like this:
Like Loading...
Tidbytez 