How to write text to a file with SQL Server

SQL Server, , , , , , , , , , , , , , , ,

The following is a tutorial on creating a stored procedure that will allow you to create a file in any directory and insert text into this newly created file.

In order for this to work you will need to authorise the running of system stored procedures with Object Linking and Embedding functionality (See OLE).

Authorisation is needed as the stored procedure we will create rely on the system SPs sp_OACreate and sp_OAMethod.

sp_OACreate: Creates an instance of an OLE object.

sp_OAMethod: Calls a method of an OLE object.

NOTE: By default, SQL Server blocks access to OLE Automation stored procedures by turning the components off as part of the security configuration for the server.

Run the script below to grant authorisation.

--AUTHORIZE SYSTEM STORED PROCEDURES
sp_configure 'show advanced options'
	,1;
GO

RECONFIGURE;
GO

sp_configure 'Ole Automation Procedures'
	,1;
GO

RECONFIGURE;
GO

--AUTHORIZED

 
Next we will create the stored procedure WriteToFile. Substitute the DatabaseName with the database you will be using.

--CREATE STORED PROCEDURE
/*
CHANGE:
DATABASE NAME: DatabaseName 
 */
USE "DatabaseName";
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('[dbo].[WriteToFile]', 'P') IS NOT NULL
	DROP PROCEDURE [WriteToFile];
GO

CREATE PROCEDURE [dbo].[WriteToFile] @File VARCHAR(255)
	,@Text VARCHAR(MAX)
	WITH EXECUTE AS CALLER
AS
BEGIN
	DECLARE @OLE INT
	DECLARE @FileID INT

	EXECUTE sp_OACreate 'Scripting.FileSystemObject'
		,@OLE OUT

	EXECUTE sp_OAMethod @OLE
		,'OpenTextFile'
		,@FileID OUT
		,@File
		,8
		,1

	EXECUTE sp_OAMethod @FileID
		,'WriteLine'
		,NULL
		,@Text

	EXECUTE sp_OADestroy @FileID

	EXECUTE sp_OADestroy @OLE
END;

 
Below is an example using the WriteToFile stored procedure.

--WRITE TO FILE EXAMPLE
/*
CHANGE:
DATABASE NAME: DatabaseName 
 */
USE "DatabaseName";
GO

DECLARE @Path VARCHAR(255)
DECLARE @Txt VARCHAR(MAX)

--NOTE: THE LOG FILE WILL BE CREATED IF IT DOES NOT ALREADY EXIST
SET @PATH = 'C:\temp\WriteToFileExampleLog.txt'
SET @Txt = 'Hello World'

EXEC [DatabaseName].[dbo].[WriteToFile] @Path
	,@Txt;

 

Using the same stored procedure here’s an example writing a table to the file using concatenation and a loop. This process works by taking the table row by row and writing the concatenated value to the file. There are easier ways to achieve this however, i.e. utilising the export functionality built into SSMS and saving this as a package to be run as a job.

--STORED PROCEDURE CREATED
--WRITE TABLE TO FILE EXAMPLE
DECLARE @Path VARCHAR(255)
DECLARE @Txt VARCHAR(MAX)
DECLARE @loopId AS INT
DECLARE @maxId AS INT
DECLARE @TempCustomer TABLE (
	ID_column INT IDENTITY PRIMARY KEY
	,FirstName NVARCHAR(100)
	);

--NOTE: THE LOG FILE WILL BE CREATED IF IT DOES NOT ALREADY EXIST
SET @PATH = 'C:\temp\WriteToFileExampleLog.txt'

INSERT INTO @tempCustomer (FirstName)
VALUES ('Paul')
	,('Jim')
	,('John')

SET @loopId = 1
SET @maxId = (
		SELECT MAX(ID_column)
		FROM @TempCustomer
		)

WHILE @loopId <= @maxId
BEGIN
	SELECT @Txt = CONVERT(VARCHAR(10), ID_column) + ', ' + FirstName
	FROM @TempCustomer
	WHERE ID_column = @loopId

	PRINT @Txt

	EXEC [TEST_DB].[dbo].[WriteToFile] @Path
		,@Txt;

	SET @loopId = @loopId + 1
END

 

How to remove NaN and Infinity from a SSRS table

SQL Server, SSRS, , , , , , , , , ,

Typically this occurs when a field uses an expression in a report table were the expression tries to divide a number by zero (Or the field can populate with #Error when there is a NULL involved). The best solution to resolve this problem is to create a custom function.

Right click on the background of your report (i.e. just below where it says Design) and go to Report Properties as shown:

picture showing user where to click in the reportThen you can left click on Code and add enter the custom code below in the window provided:

Public Function Divide(ByVal dividend As Double, ByVal divisor As Double) As Double
   If IsNothing(divisor) Or divisor = 0 Or IsNothing(dividend) Or dividend = 0 Then
      Return 0
   Else
      Return dividend / divisor
   End If
End Function

IsNothing() is used to avoid #Error and check whether the divisor or the dividend are 0 to avoid NaN and Infinity.

Now change the field expression from, for example:

SUM(Fields!A.Value)/SUM(Fields!B.Value)

To the expression below using the newly defined function Divide:

= Code.Divide(Sum(Fields!A.Value), Sum(Fields!B.Value))

Next you can update the fields textbox properties to make the number display as a percentage.

NOTE: Inserting a user defined function into a table will mean that that table will no longer be able to be copied and pasted as this throws an error. To copy and past the table you will need to look at the code of the report by right clicking on the report and choosing View Code. Search for “Code.Divide” and comment it out with an apostrophe ( ‘ ). You will now be able to copy the table.

How to save game scores in Corona SDK by writing to a file.

Corona SDK, Game Development, Lua, , , , , , , , , , , , ,

The scope of this tutorial will be to record and store the highest score achieved by a player in a game. This is done by:

  • Creating a file with a score of zero where the file does not exists.
  • When a player dies:
    • The previous score, i.e. value within the file, is assigned to a variable.
    • The player’s new score is assigned to a variable.
  • The two variables are compared.
  • If the previous score is higher nothing is changed.
  • If the previous score is lower the new score will be written to the file in its place.

Note:

No UI is provided as part of the tutorial as corona sdk often changes the manner in which objects are displayed, meaning the UI could break with future corona sdk versions. We will be working from the simulator output window alone.

For security reasons, you are not allowed to write files in the system.ResourceDirectory (the directory where the application is stored). You must specify either system.DocumentsDirectory, system.TemporaryDirectory, or system.CachesDirectory in the system.pathForFile() function when opening the file for writing. Read move about this here.

Below is a table describing when and where each directory should be used.

systemDirectoriesTo use this tutorial create a folder containing a main.lua file.
Paste the code below into the file and save.
Open the file with Corona SDK and the score.txt file will be created and populated with a score of zero.

Play around with the Player Score variable:
newScore = 99

Enter a higher score and it will overwrite what currently exists in the file.

Enter a lower score and nothing will be changed.

-- main.lua

local path = system.pathForFile( "score.txt", system.DocumentsDirectory )

deleteFile = function()
 local result, reason = os.remove(path) 
	if result then
		print( "File removed" )
	else
		print( "File does not exist", reason )  --> score.txt: No such file or directory
	end
end

--[[ 
Uncomment below to remove file
--]]

--deleteFile()

-- Player Score

newScore = 99

-- io.open opens a file at path. returns nil if no file found
-- fh short for file handle
-- "r" is the read instruction
local fh, reason = io.open( path, "r" )

if fh then
    -- Read all contents of file into a variable oldScore
	-- This will be the previous score
	-- To read file content as number use "*number"
	-- To read file content as text use "*a"
	-- "\n" new line
    local oldScore = fh:read( "*number" )
    print( "Old contents of " .. path .. "\n" .. oldScore )
	
	if oldScore < newScore then
		-- re-opening the file in "w+" mode will erase all previous data stored in the file
		-- in the comments below is a table listing all the file mode types
		fh = io.open( path, "w+" )
		-- Set the score to the player's new score.
		fh:write( newScore )
		print( "New contents of " .. path .. "\n" .. newScore )
	end	
else
	-- Error logic
    print( "Reason open failed: " .. reason )  -- display failure message in terminal

    -- create file because it doesn't exist yet
    fh = io.open( path, "w" )

    if fh then
        print( "Created file" )
    else
        print( "Create file failed!" )
    end
	
	-- Set the score to zero.
    fh:write( 0 )

end

io.close( fh )

--[[
The various file modes are listed in the following table:

"r"	Read-only mode and is the default mode where an existing file is opened.
"w"	Write enabled mode that overwites existing file or creates a new file.
"a"	Append mode that opens an existing file or a creates a new file for appending.
"r+" Read and write mode for an existing file.
"w+" All existing data is removed if file exists or new file is created with read write permissions.
"a+" Append mode with read mode enabled that opens an existing file or creates a new file.

]]--

See the lua online book’s I/O library section for more information on working with files.

How to export a table as an XML file using a SQL script

Database Administrator, dba, sql, SQL Server, T-SQL, , , , , , , , , , , , , , , ,

Below is a script that will allow you, as the title suggests, to export a table as an XML file.

By use of the find and replace function in your preferred text editor, or by way of the text editor in SSMS, input your values for the required fields, i.e. replace TableName with the name of the table you will be working off.

SERVER NAME: ServerName

DATABASE NAME: DatabaseName

SCHEMA NAME: SchemaName

TABLE NAME: TableName

SAVE LOCATION ADDRESS: AddressName

FILE NAME: FileName (Excluding .xml extension)

Then run the script below and the table will be exported as an XML file to the location and file name of your choosing.

/*
FIND AND REPLACE:

SERVER NAME: ServerName
DATABASE NAME: DatabaseName 
SCHEMA NAME: SchemaName
TABLE NAME: TableName
SAVE LOCATION ADDRESS: AddressName
FILE NAME: FileName
 */
-- Run on ServerName
USE DatabaseName;
GO

DECLARE @saveLocation AS VARCHAR(510)
DECLARE @nameOfFile AS VARCHAR(255)
DECLARE @instanceName AS VARCHAR(255)
DECLARE @cmd NVARCHAR(1020)

SET @saveLocation = 'AddressName'
SET @nameOfFile = 'FileName'
SET @saveLocation = @saveLocation + '\' + @nameOfFile + '.xml'
SET @instanceName = (
		SELECT @@servername
		)

-- Please note that the fully qualified table name is needed
SELECT @cmd = '
    bcp "SELECT * FROM [DatabaseName].[SchemaName].[TableName] row for xml auto, root(''rows''), elements" ' + 'queryout "' + @saveLocation + '" -S ' + @instanceName + ' -T -w -r -t';

EXEC xp_cmdshell @cmd;
GO

 

This script is enabled by two utilities xp_cmdshell and BCP.

For an explanation of each please view the associated links.

What is BCP?

SQL Server, , ,

BCP (bulk copy program) is a utility that installs with SQL Server and can assist with large data transfers.

“The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns.” – Microsoft

To see the parameter options from the command line on a machine with SQL Server installed, type “bcp” and press Enter.

bcp in c.m.d. windowFor further information please see the LINK.

How to enable and disable xp_cmdshell

Database Administrator, dba, sql, SQL Server, T-SQL, , , , , , , , , , , , , , , , , , , ,

xp_cmdshell is an extended stored procedure provided by Microsoft and stored in the master database. This procedure allows you to issue operating system commands directly to the Windows command shell via T-SQL code.

By default, the xp_cmdshell option is disabled on new installations. Along with other unwanted behavior malicious users can elevate their privileges with the use of xp_cmdshell. For this reason it is generally recommend to leave xp_cmdshell disabled. It can be enabled by using the Policy-Based Management or by running the sp_configure system stored procedure as shown in the following code example:

Use Master
GO

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

 

To disable xp_cmdshell use the following code example. Note: The following example also sets show advanced options server configuration option to 0. It is best practice to leave this option in the default state of 0 to stop the enabling of features.

Use Master
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

How to populate a temp table with the days in a month

sql, SQL Server, T-SQL, , , , , , , , , , , , ,

As described in the title this is a dynamic sql script that will populate a temp table with the day dates of the current month as outputted below:

calendarTableTo create a temporary calendar table for a greater period of time than just the current month:

Set @startDate to any historical date and @numberOfLoops to any number you like (365 being a year from the @startdate).

To make the table persistent:

Remove the # signs from the #calendarTable temp table referenced and rename the table accordingly.

You could also remove any columns you do not need.

IF OBJECT_ID('tempdb..#calendarTable') IS NOT NULL
	DROP TABLE #calendarTable;
	GO

CREATE TABLE #calendarTable (
	"DayID" INT
	,"DayOfMonth" INT
	,"DayOfQuarter" INT
	,"DayOfYear" INT
	,"MonthOfDate" INT
	,"YearOfDate" INT
	,"DayDate" DATE
	,PRIMARY KEY (DayID)
	)

DECLARE @startDate AS DATE
DECLARE @baseDate DATE
	,@offSet INT
	,@numberOfLoops INT

/*
To create a temporary calendar table:
Set @startDate to any historical date 
and @numberOfLoops to any number you like 
(365 being a year from the @startdate)

To make the table persistent remove the # signs 
from the #calendarTable temp table referenced 
*/
SET @startDate = GETUTCDATE() /*'20150101'*/
SET @baseDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate), 0)
SET @offSet = 0
SET @numberOfLoops = DATEDIFF(DAY, @baseDate, DATEADD(MONTH, 1, @baseDate)) /*365*/

WHILE (@offSet < @numberOfLoops)
BEGIN
	INSERT INTO #calendarTable (
		"DayID"
		,"DayOfMonth"
		,"DayOfQuarter"
		,"DayOfYear"
		,"MonthOfDate"
		,"YearOfDate"
		,"DayDate"
		)
	SELECT (@offSet + 1)
		,DATEPART(DAY, DATEADD(DAY, @offSet, @baseDate))
		,DATEDIFF(D, DATEADD(QQ, DATEDIFF(QQ, 0, DATEADD(DAY, @offSet, @baseDate)), 0), DATEADD(DAY, @offSet, @baseDate)) + 1
		,DATEPART(DAYOFYEAR, DATEADD(DAY, @offSet, @baseDate))
		,DATEPART(MONTH, DATEADD(DAY, @offSet, @baseDate))
		,DATEPART(YEAR, DATEADD(DAY, @offSet, @baseDate))
		,DATEADD(DAY, @offSet, @baseDate)

	SET @offSet = @offSet + 1
END

SELECT *
FROM #calendarTable

How to fix an SSRS Report that cannot find stored procedure fields or parameters while displaying the define query parameters window

SSRS Reporting, , , , , , , , , , , , , , , , ,

When setting up your data sets in an SSRS report if you are using a complicated stored procedure, i.e. a SP which relies on dynamic SQL, temp tables or finishes with an IF statement, chances are the SSRS report will not be able to figure out what the SP returns. When this happens you will not be able to populate the data set with data.

This happens because the execution plan of the SSRS software isn’t smart enough and won’t be able to determine what fields the SP creates and therefore will not be able to create a means of storing the data on the report end.

Subsequently you’ll see the, often misleading, table below popup.

 

SSRS Pop up Define Query Parameters

The solution to stop this from happening is quite simple, but considering how expensive this software is it’s a solution that shouldn’t have to be employed.

The solution is to trick the SSRS software by simplifying the SP. Basically perform a select on the specific fields you need with no additional logic or create a table with the exact fields you need with corresponding data types and select from that.

Use this dumb SP to populate the dataset in the SSRS report.

In the “Choose a data source and create a query” window as below, click refresh fields.

SSRS window refresh fields
The software should now pick up the fields.
Change the SP back to the way it was before and do not refresh the fields again in the SSRS software and the fields should continue to populate as you need them.

How to pass a multi-value parameter to a stored procedure from a SSRS Report

Reporting, SQL Server, SSRS, T-SQL, , , , , , , , , , , , , , , , , , , ,

When you allow for multiple field values to be selected in a SSRS report there needs to be additional logic added to the back end to deal with this.

This is best explained with an example scenario.

I have a table called Ireland with two columns, ID_Column and County. You can use the script below to create and populate this table. Run the query below to follow the working example.

CREATE DATABASE [TEST_DB];

USE [TEST_DB];
GO

/****** Object:  Table [dbo].[Ireland]    Script Date: 07/15/2015 10:49:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Ireland] (
	[ID_Column] [int] IDENTITY(1, 1) NOT NULL
	,[County] [varchar](9) NULL
	,PRIMARY KEY CLUSTERED ([ID_Column] 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

SET IDENTITY_INSERT [dbo].[Ireland] ON

INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (1, N'Antrim')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (2, N'Armagh')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (3, N'Carlow')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (4, N'Cavan')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (5, N'Clare')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (6, N'Cork')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (7, N'Derry')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (8, N'Donegal')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (9, N'Down')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (10, N'Dublin')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (11, N'Fermanagh')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (12, N'Galway')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (13, N'Kerry')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (14, N'Kildare')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (15, N'Kilkenny')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (16, N'Laois')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (17, N'Leitrim')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (18, N'Limerick')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (19, N'Longford')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (20, N'Louth')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (21, N'Mayo')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (22, N'Meath')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (23, N'Monaghan')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (24, N'Offaly')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (25, N'Roscommon')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (26, N'Sligo')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (27, N'Tipperary')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (28, N'Tyrone')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (29, N'Waterford')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (30, N'Westmeath')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (31, N'Wexford')
INSERT [dbo].[Ireland] ([ID_Column], [County]) VALUES (32, N'Wicklow')
SET IDENTITY_INSERT [dbo].[Ireland] OFF

 

If I wanted to allow a user to select every county from the Ireland table in an SSRS report I would create a stored procedure that simply executes the following query.

SELECT * FROM Ireland

 

However an SSRS report which allows users to choose counties in Ireland like below means that a WHERE clause needs to be introduced and be able to respond to the field values selected.

Image showing an SSRS report parameter options

But the issue is that the SSRS report will pass the multi-valued parameter as a string with the values separated by a comma.

So instead of receiving the required: ‘Antrim’, ‘Armagh’, ‘Carlow’, ‘Cavan’ etc. for use in the WHERE clause.

SQL Server is passed: ‘Antrim, Armagh, Carlow, Cavan’ etc. which cannot be used.

So the first additional logic and code to be added to the back end to deal with the multi-value parameter is a User Defined Function (UDF) which splits the parameter. The following function and quotations are taken from the 4guysfromrolla website.

Function Scope:

“There are generally two parameters to a split function: the list to split and the character(s) to split on, the delimiter. In the following function we begin by declaring our input variables – @List, the list to split, and @SplitOn, the delimiter(s) to split on. The return value of this UDF is a table with two fields: Id, an identity column, and Value, an nvarchar(100) column.”

Function Logic:

“The main body of the function simply loops through the string finding the first occurrence of the delimiter on each pass. Once the delimiter has been found, the string is broken into two pieces. The first piece is inserted into the result table while the second piece replaces the original list. The loop continues until no more occurrences of the delimiter are found. Lastly, the remainder of the list is added to the result table. Return the table and you have a split function.”

Run the query below to follow the working example.

USE [TEST_DB];
GO

CREATE FUNCTION [dbo].[Split] (
	@List NVARCHAR(2000)
	,@SplitOn NVARCHAR(5)
	)
RETURNS @RtnValue TABLE (
	Id INT identity(1, 1)
	,Value NVARCHAR(100)
	)
AS
BEGIN
	WHILE (Charindex(@SplitOn, @List) > 0)
	BEGIN
		INSERT INTO @RtnValue (value)
		SELECT Value = ltrim(rtrim(Substring(@List, 1, Charindex(@SplitOn, @List) - 1)))

		SET @List = Substring(@List, Charindex(@SplitOn, @List) + len(@SplitOn), len(@List))
	END

	INSERT INTO @RtnValue (Value)
	SELECT Value = ltrim(rtrim(@List))

	RETURN
END

 

(For a line by line break down of the function please see the webpage.)

Now that the split function exits within the database it is now possible to use a Stored Procedure to SELECT from the Ireland table pulling back specific counties by way of a WHERE clause.

Run the query below to follow the working example.

USE Test_DB;
GO

IF OBJECT_ID('[GetCounties]') IS NULL
	EXEC ('CREATE PROCEDURE dbo.[GetCounties] AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[GetCounties] @County VARCHAR(MAX)
AS
	/*

NAME OF SP: GetCounties
Author:		Bloggins86
Date:		15/07/2015
Purpose:	Test multi-parameter select

*/
	------------------------------------------------------
	------------------------------------------------------
	--INSERT STORED PROCEDURE LOGIC HERE
	SELECT *
	FROM Ireland
	WHERE County IN (SELECT Value FROM dbo.Split(@County, ','))

	------------------------------------------------------
	------------------------------------------------------

 

Now that the populated table, split function and county select SP exists run the query below passing a string with multiple counties to return Dublin, Meath and Cork from the Ireland Table.

EXEC dbo.[GetCounties] 'Dublin, Meath, Cork'

 

You should now have returned the table with Dublin, Meath and Cork as separate row entries.

And that’s it, thanks for reading.

Combine and then split tsql insert statement files into batches of one thousand.

Batch Script, Reporting, sql, SQL Server, T-SQL, , , , , , , ,

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)