T-SQL Bulk Insert-Output Part 4

This entry is part 4 of 6 in the series T-SQL Bulk Insert

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.

1USE [myDatabase]
2GO
3SET ANSI_NULLS ON
4GO
5SET QUOTED_IDENTIFIER ON
6GO
7 
8CREATE PROCEDURE [dbo].[SelectSimpleBulkInsertHeaderTrailer] 
9    -- Add the parameters for the stored procedure here
10    @SequenceNumber int
11AS
12BEGIN
13    -- SET NOCOUNT ON added to prevent extra result sets from
14    -- interfering with SELECT statements.
15    SET NOCOUNT ON;
16 
17    DECLARE @TotalAmount int
18    SELECT @TotalAmount = SUM(cast(Amount as int)) FROM dbo.SimpleBulkInsert
19    DECLARE @AB nvarchar(1)
20    SELECT TOP 1 @AB = [CodeAB] FROM dbo.SimpleBulkInsert
21    DECLARE @DetailLineNumber int
22    SELECT @DetailLineNumber = Count(*) FROM dbo.SimpleBulkInsert
23    DECLARE @JulianDate as nvarchar(7)
24    SELECT TOP 1 @JulianDate = [JulianDate] FROM dbo.SimpleBulkInsert
25 
26    SELECT cast('H' as CHAR(1)) 
27        + cast ('ABC COMPANY         ' AS CHAR(20))
28        + cast(@JulianDate AS CHAR(7))
29        + cast (' ' AS CHAR(1))
30        + right('0000000' + cast(@SequenceNumber AS varchar(7)),7)
31        + cast (' ' AS CHAR(1))
32        + cast ('X4HT8NMG8' AS CHAR(9))
33    UNION ALL
34    SELECT Cast('0000' AS CHAR(4))   
35            + Cast(CodeAB AS CHAR(1)) 
36            + right('0000' + cast((row_number ()  over (order by LastName)) + 1 as varchar (4)),4) 
37            + cast(JulianDate AS CHAR(7))
38            + cast('  ' AS CHAR(2))
39            + cast('A_CONSTANT' AS CHAR(12))
40            + right('000000000000' + cast(Amount AS varchar(12)),12)
41            + left(cast((FirstName + ' ' + LastName) AS char(16)),16)
42            + Cast ('END' AS CHAR(3))
43    FROM dbo.SimpleBulkInsert
44    UNION ALL
45    SELECT 'T'
46        + cast(@AB as char(1))
47        + right('00000' + cast (@DetailLineNumber AS varchar(5)),5)
48        + cast(' ' AS CHAR(1))
49        + right('000000000000' + cast(@TotalAmount AS varchar(12)),12)
50        + cast(' ' AS CHAR(1))
51        + cast (@JulianDate as char(7))
52    FROM dbo.SimpleBulkInsert
53END
54GO

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.

1-------------------------------------------------------------
2HABC COMPANY         2017029 0000047 X4HT8NMG8
30000A00022017029  A_CONSTANT  000000098341John Johnson    END
40000A00032017029  A_CONSTANT  000000000981Sally SalimanderEND
50000A00042017029  A_CONSTANT  000000023098Bob Smith       END
6TA00003 000000122420 2017029
7TA00003 000000122420 2017029
8TA00003 000000122420 2017029
9 
10Return Value
11------------
120
13 
14(1 row(s) affected)

Series Navigation<< T-SQL Bulk Insert-Output Part 3T-SQL Bulk Insert-Output Part 5 >>

BeginCodingNow.com

for data analysts & software developers

for data analysts & software developers