This post continues from our second post called T-SQL Bulk Insert Part 2.

Let’s carry on from that point and work on our trailer. Information in a trailer line typically describes the detail lines by including things like a row count and any totals.

The requirements for the trailer line include starting that line with the letter “T” for Trailer, then either the “A” or “B” code should be shown, the total number of detail lines in the file (five characters with leading zeros), one space, the total of the amount in all of the detail lines and the Julian Date in its seven digit format of (YYYYDDD). The provider of the file assures us that the Julian dates in all of the detail lines will always be the same, so we can just read the first on in the list and use that date in the trailer. The same rule applies to the “A” or “B” codes. The header line’s data will always match the detail line’s data.

USE [myDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SelectSimpleBulkInsertHPTrailer] 
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
	DECLARE @AB nvarchar(1)
	SELECT TOP 1 @AB = [CodeAB] FROM dbo.SimpleBulkInsert
	DECLARE @DetailLineNumber int
	SELECT @DetailLineNumber = Count(*) FROM dbo.SimpleBulkInsert
	DECLARE @JulianDate as nvarchar(7)
	SELECT TOP 1 @JulianDate = [JulianDate] 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 'T' 
		+ cast(@AB as char(1))
		+ right('00000' + cast (@DetailLineNumber AS varchar(5)),5)
		+ cast(' ' AS CHAR(1))
		+ right('000000000000' + cast(@TotalAmount AS varchar(12)),12)
		+ cast(' ' AS CHAR(1))
		+ cast (@JulianDate as char(7))
	FROM dbo.SimpleBulkInsert  
END
GO

The output is shown below. The only problem is that the trailer query displayed twice.

-------------------------------------------------------------
Header Placeholder - to be programmed later...
0000A00022017029  A_CONSTANT  000000098341John Johnson    END
0000A00032017029  A_CONSTANT  000000023098Bob Smith       END
TA00002 000000121439 2017029
TA00002 000000121439 2017029

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

(1 row(s) affected)