This post continues from our other post called T-SQL Bulk Insert-Output Part 5.

In this post we will be using temporary variables, which are a type of temporary tables in SSMS.

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

	DECLARE @tempheader table (Column1 char(70));
    DECLARE @tempdetails table (Column1 char(70));
	DECLARE @temptrailer table (Column1 char(70));
	DECLARE @tempall table (Column1 char(70));

	INSERT INTO @tempheader
		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))

	INSERT INTO @tempdetails 
		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

	INSERT INTO @temptrailer
		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
	
	INSERT INTO @tempall SELECT * FROM @tempheader
	INSERT INTO @tempall SELECT * FROM @tempdetails
	INSERT INTO @tempall SELECT * FROM @temptrailer
	
	SELECT * FROM @tempall
END
GO

The output is as follows.

----------------------------------------------------------------------
HABC COMPANY         2017029 0000065 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)