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