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
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
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
