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

Let’s carry on from that point and work on our header. We are sending this file to another system so we need to tell it some information about us. First of all the header line will start with an “H”. Next it will have some information about our company so we will put in our company name, left justified in 20 spaces with excess spaces being filled with blanks. Next we will put the Julian Date. Add one space. Then we will put a sequence number that changes each time we produce this file. To do this we will set up a parameter. This number will occupy 7 spaces and be proceeded by zeros on the left. Add one space. Finally we will put a code of characters that represents our unique company’s account number.

When we execute our stored procedure we see the window that asks us for our parameter value. Itis shown below. Here the user has entered in the number 47. At the bottom of this window there is an OK button that the user clicks to get the results.

Our stored procedure code is shown below. Again, some of the comments and a few spaces have been removed.

USE [myDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SelectSimpleBulkInsertHeaderTrailer] 
	-- Add the parameters for the stored procedure here
	@SequenceNumber int
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 cast('H' as CHAR(1)) 
		+ cast ('ABC COMPANY         ' AS CHAR(20))
		+ cast(@JulianDate AS CHAR(7))
		+ cast (' ' AS CHAR(1))
		+ right('0000000' + cast(@SequenceNumber AS varchar(7)),7)
		+ cast (' ' AS CHAR(1))
		+ cast ('X4HT8NMG8' AS CHAR(9))
	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

I have added one more record to the flat csv file just to test it a little further. Below is the result file in SQL Server. It is still producing one trailer line for each detail line, however.

-------------------------------------------------------------
HABC COMPANY         2017029 0000047 X4HT8NMG8
0000A00022017029  A_CONSTANT  000000098341John Johnson    END
0000A00032017029  A_CONSTANT  000000000981Sally SalimanderEND
0000A00042017029  A_CONSTANT  000000023098Bob Smith       END
TA00003 000000122420 2017029
TA00003 000000122420 2017029
TA00003 000000122420 2017029

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

(1 row(s) affected)