question

Kelvin Phayre avatar image
Kelvin Phayre asked

Generate line numbers from selected data SQL Server 2000

From a select statement I want to generate a unique line number (increments of 10000) for every record returned.

EG

select ?? as [LineNO], Customer from Customers

If the Customer table had 5 records I want the following returned.

LineNo Customer
10000 CUST1
20000 CUST2
30000 CUST3
40000 CUST4
50000 CUST5

t-sqlsql-server-2000row-countsgenerate-sequence
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

try something like

SELECT  
ROW_NUMBER() OVER (ORDER BY customer ASC)*10000 as LineNumber,
Customer
FROM [customers] 
ORDER BY customer ASC

[Edit - for SQL 2000]
hope this example helps you

USE [adventureworks]
GO

-- create new (identical) table with zero rows
SELECT TOP 1
* INTO [Sales].NewCustomer
FROM [Sales].[Customer] AS c
WHERE 1=2

go
-- remove existing identity column as the settings are wrong
ALTER TABLE [Sales].newcustomer
DROP COLUMN CustomerID
GO
-- add column with identity settings to give numbers
ALTER TABLE sales.newcustomer
ADD CustomerID INT IDENTITY (10000,10000)
go

-- put the data into the new table
INSERT INTO [Sales].newcustomer 
SELECT 
    [c].[TerritoryID] ,
    [c].[AccountNumber] ,
    [c].[CustomerType] ,
    [c].[rowguid] ,
    [c].[ModifiedDate] FROM [Sales].[Customer] AS c 
    ORDER BY [c].[CustomerID] ASC
go

-- show the new data
SELECT [Sales].[NewCustomer].[TerritoryID] ,
    [Sales].[NewCustomer].[AccountNumber] ,
    [Sales].[NewCustomer].[CustomerType] ,
    [Sales].[NewCustomer].[rowguid] ,
    [Sales].[NewCustomer].[ModifiedDate] ,
    [Sales].[NewCustomer].[CustomerID] FROM [Sales].newcustomer
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 - Only thing to note is that this will not necessarily order the line numbers in the order that the data is returned - you must specify an overall ORDER BY to guarantee that.
1 Like 1 ·
Added ORDER BY as Matt suggests but there is still absolutely no correlation between CUST1 and 10000, it could easily end up as Cust39 and 500000. You'd need to do some string parsing to take the CustNo and append 4 zeros for that.
1 Like 1 ·
Really sorry but I'm using SS 2000 which can't use ROW_NUMBER(). Tried it on SS 2005 and it works a treat. I'm doing a data migration from 2000 to 2005. Any 2000 solution out there?
0 Likes 0 ·
OK, I'll take this opportunity to ask, do you want consecutive linenumber values or a linenumber value that has 0000 appended to the customer number?
0 Likes 0 ·
The customer code is a red herring I was just using it as an example. I'm extracting from an ERP system Ledger transactions. The import to the new ERP requires a unique line number for each tran. I extract my data using a select statement. At this point I can't fill the [Line No] field. This I am doing manually using Excel. What would be nice is if I could populate the [Line No] field also. The first line being 10000 and then next 20000 with increments of 10000. The output is copied into a special Excel spreadsheet.
0 Likes 0 ·
Show more comments
Blackhawk-17 avatar image
Blackhawk-17 answered

Well one possibility is to create a temp table with an identity column (10000, 10000) and then insert your new data into that. SELECT from the temp table for your import.

Greg

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.