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