MSSQL 2K -- Select Statement Help

[heather_j92002]

Is there a way to generate a user defined auto incrementing number in a select statement as a new column?




CREATE TABLE [dbo].[Customers] (
[CustomerNumber] [numeric](18, 0) NULL ,
[LName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO



My Example:

Table = Customers

CustomerNumber LName FName MName UID

123 Smith Bob J 1
456 Jones Cindy K 2
987 Taylor Karl N 3


My query that does not work:
Select CustomerNumber,
LName,
Fname,
MName,
'059'
+
-- ? Incrementing Number beginning at 1000 ?
as UID
--Into NewCustomers (when I figure this out)
From Customers

I would hope to see the following result set:

123 Smith Bob 0591000
456 Jones Joe 0591001
987 Taylor Cindy 0591002

[MAK]

drop table [Customers]

CREATE TABLE [dbo].[Customers] (
[CustomerNumber] [numeric](18, 0) NULL ,
[LName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UID] [int] IDENTITY (1000, 1) NOT NULL
) ON [PRIMARY]
GO

insert into customers (CustomerNumber, LName, FName, MName) select 123, 'Smith', 'Bob', 'J'
insert into customers (CustomerNumber, LName, FName, MName) select 456, 'Jones','Cindy', 'K'
insert into customers (CustomerNumber, LName, FName, MName) select 987, 'Taylor', 'Karl', 'N'

select * from customers
alter table customers add UID2 as '059'+convert(varchar(20),UID)
select * from customers