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

All we are going to do here is use the SQL keyword DISTINCT to remove the duplicate trailer lines. Our stored procedure is now called SelectSimpleBulkInsertHTNoDupT. There are no duplicate trailer lines now. We are not using temporary tables here. We are using the UNION keyword.

The output from SSMS is shown at the bottom of this post. In the upper right corner of the output window there is a small icon that has two angle brackets. If you click on it you will see the output in an pop-up window. Click in the window somewhere and select all of the text (in Windows use Ctrl+A).

USE [myDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SelectSimpleBulkInsertHTNoDupT] 
	-- 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 DISTINCT '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

Here is the output. Another thing to notice about the output is that the header, detail and trailer lines are all of different lengths. In our requirements we wanted this. Sometimes the requirements will want all the lengths to be the same. For example, the client may want all of the lengths to be 80 characters. If this is the case you could use temporary variables if you wanted to.

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

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

(1 row(s) affected)