Our query will create sequence numbers. This is part 4. We other posts called SQL Server Sequential Numbers as well.

In this post we are going to use a different type of example. In this example we need to take a table and create the data for a text file that has two header lines, the detail lines and one trailer line.

Table

Our table is called DetailLines. It has one column called TwoChars that has a data type of chars(2). We only need a little bit of data so we have added three rows as follows: ON, BC, and AL.

Code

We will also be using cast(), right() and union all, as well as other statements.

declare @headernumber char(1) = '2'
select cast('HDR1' as char(4))
union all
select distinct cast('HDR' as char(3)) + cast(@headernumber as char(1))
union all
select cast(right('00' + cast((row_number() over (order by TwoChars)) as varchar(2)),2) as char(2))
      + cast(TwoChars as char(2)) 
	  from dbo.DetailLines
union all
select cast('TRL0' as char(4))

Here is the output in SSMS when Results to Text is selected.

----
HDR1
HDR2
01AL
02BC
03ON
TRL0

(6 rows affected)

ROW_NUMBER is a temporary value calculated when the query is run. To persist numbers in a table, see IDENTITY Property and SEQUENCE. If it is a temporary variable we could change the sequencing to count by four’s if we wanted to. By default it starts at one and increments by one. Here is the change to start the counting at 2 and increase by 4 for each row. Just multiply by 4. Notice that the right() function puts leading zeros in our sequence number.

Insert Into

The INSERT INTO SELECT statement copies data from one table and inserts it into another table.