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.
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.
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.
The INSERT INTO SELECT statement copies data from one table and inserts it into another table.