LOL
Yes that is easier.
Identity function has 2 parameters (when you use it in structure description)
IDENTITY(initial value, increment)
So you could use it:

DECLARE @Test TABLE ( UniqueField Int IDENTITY(100,1))

If you want that field to strat from 100.


>OK, I thought you were implying it would be very slow if there were lots of fields. FWIW, there's a very easy way to get the structure and the list of fields in SQL Server Management Studio. Just right click on the table and select Script table as | Create to or Select to. So I managed to adjust your code in a few minutes.
>
>Is there any way to specify what number an identity field starts generating at?
>
>>This depends of you :-) Are you too lazy to write all 40 in that query?
>>BTW there is a easy way to put ALL fields in query:
>>write:
>>

>>SELECT
>>

>>then drag Columns node from Object explorer and drop it right after SELECT. That will build you SELECT (field list here)
>>Then you must find and delete field in question. Only anoynig thing is when you build a Table variable, becuase you need to set field types.
>>
>>
>>>Thanks Borislav,
>>>
>>>The table has 40 odd fields. Is that too many?
>>>
>>>>How many fields you have in that table? If you have a relatively small number of fields you could use something like that:
>>>>

>>>>DECLARE @Test TABLE ( stucture as your original table except the field you want to be unique, UniqueField Int IDENTITY(1,1))
>>>>DECLARE @i int
>>>>SET @i = 0
>>>>WHILE @i < 10 && or whatever number you need to get 1.5 mil. records
>>>>      BEGIN
>>>>          INSERT INTO @Test (field list here EXCEPT UniqueField)
>>>>          SELECT (field list here EXCEPT UniqueField)
>>>>                FROM MyTable
>>>>          SET @i = @i + 1
>>>>      END
>>>>INSET INTO MyTable
>>>>SELECT (field list here EXCEPT UniqueField), CAST(UniqueField as varchar(50)) FROM @Test
>>>>