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)