This post continues from our first post T-SQL Bulk Insert-Output of Text Files.

Here we will create a new stored procedure that does a bit more than the previous post discussed. Now we will add a header and trailer line to our file. The trailer will include the total of the Amount column. Out new stored procedure will be called SelectSimpleBulkInsertHPTT (HPTT stands for Header Placeholder Trailer Total).

The code is shown below. I removed some of the comments so that it fits better on the browser. Notice that we can run several SELECT queries and combine them with the UNION ALL statement. We needed to declare the total amount as an integer to be able to get a SUM. We then needed to convert it back to characters to put into the output.

USE [myDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SelectSimpleBulkInsertHPTT] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @TotalAmount int
    SELECT @TotalAmount = SUM(cast(Amount as int)) FROM dbo.SimpleBulkInsert
	
	SELECT 'Header Placeholder - to be programmed later...'
	UNION ALL
	SELECT Cast('0000' AS CHAR(4))   
			+ Cast(CodeAB AS CHAR(1)) 
			+ right('0000' + cast((row_number ()  over (order by LastName)) + 1 as varchar (4)),4) 
			+ cast(JulianDate AS CHAR(7))
			+ cast('  ' AS CHAR(2))
			+ cast('A_CONSTANT' AS CHAR(12))
			+ right('000000000000' + cast(Amount AS varchar(12)),12)
			+ left(cast((FirstName + ' ' + LastName) AS char(16)),16)
			+ Cast ('END' AS CHAR(3))
	FROM dbo.SimpleBulkInsert
	UNION ALL
	SELECT 'Total is: ' + cast(@TotalAmount as varchar(20))
    
END
GO

The output is shown below. It is in the format that we want.

-------------------------------------------------------------
Header Placeholder - to be programmed later...
0000A00022017029  A_CONSTANT  000000098341John Johnson    END
0000A00032017029  A_CONSTANT  000000023098Bob Smith       END
Total is: 121439

Return Value
------------
0

(1 row(s) affected)

Just for our reference the original input file is shown below.

"FirstName","LastName","A","Amount","JulianDate"
Bob,"Smith",A,23098,2017029
John,Johnson,A,98341,2017029