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)