How to drop IDENTITY property of column in SQL Server 2005

You could try

SET IDENTITY_INSERT #Tmp_MyTable ON 
-- ... do stuff
SET IDENTITY_INSERT #Tmp_MyTable OFF

This will allow you to select into #Tmp_MyTable even though it has an identity column.

But this will not work:

-- Create empty temp table
SELECT *
INTO #Tmp_MyTable
FROM MyTable
WHERE 1=0
...
WHILE ...
BEGIN
    ...
    SET IDENTITY_INSERT #Tmp_MyTable ON 

    INSERT INTO #Tmp_MyTable
    SELECT TOP (@n) *
    FROM MyTable

    SET IDENTITY_INSERT #Tmp_MyTable OFF 
    ...    
END

(results in the error "An explicit value for the identity column in table '#Tmp' can only be specified when a column list is used and IDENTITY_INSERT is ON.")

It seems there is no way without actually dropping the column - but that would change the order of columns as OP mentioned. Ugly hack: Create a new table based on #Tmp_MyTable ...

I suggest you write a stored procedure that creates a temporary table based on a table name (MyTable) with the same columns (in order), but with the identity property missing.

You could use following code:

select t.name as tablename, typ.name as typename, c.*
from sys.columns c inner join
     sys.tables t on c.object_id = t.[object_id] inner join
     sys.types typ on c.system_type_id = typ.system_type_id
order by t.name, c.column_id

to get a glimpse on how reflection works in TSQL. I believe you will have to loop over the columns for the table in question and execute dynamic (hand-crafted, stored in strings and then evaluated) alter statements to the generated table.

Would you mind posting such a stored procedure for the rest of the world? This question seems to come up quite a lot in other forums as well...

EDIT Toggling IDENTITY_INSERT as suggested by Daren is certainly the more elegant approach, in my case I needed to eliminate the identity column so that I could reinsert selected data into the source table

The way that I addressed this was to create the temp table just as you do, explicitly drop the identity column, and then dynamically build the sql so that I have a column list that excludes the identity column (as in your case so the proc would still work if there were changes to the schema) and then execute the sql here's a sample

declare @ret int
Select * into #sometemp from sometable
Where
id = @SomeVariable

Alter Table #sometemp Drop column SomeIdentity 

Select @SelectList = ''
Select @SelectList = @SelectList 
+ Coalesce( '[' + Column_name + ']' + ', ' ,'')
from information_schema.columns
where table_name = 'sometable'
and Column_Name <> 'SomeIdentity'

Set @SelectList = 'Insert into sometable (' 
+ Left(@SelectList, Len(@SelectList) -1) + ')'
Set @SelectList = @SelectList 
+ ' Select * from #sometemp '
exec @ret  =  sp_executesql  @selectlist

IF you are just processing rows as you describe, wouldn't it be better to just select the top N primary key values into a temp table like:

CREATE TABLE #KeysToProcess
(
     TempID    int  not null primary key identity(1,1)
    ,YourKey1  int  not null
    ,YourKey2  int  not null
)

INSERT INTO #KeysToProcess (YourKey1,YourKey2)
SELECT TOP n YourKey1,YourKey2  FROM MyTable

The keys should not change very often (I hope) but other columns can with no harm to doing it this way.

get the @@ROWCOUNT of the insert and you can do a easy loop on TempID where it will be from 1 to @@ROWCOUNT

and/or

just join #KeysToProcess to your MyKeys table and be on your way, with no need to duplicate all the data.

This runs fine on my SQL Server 2005, where MyTable.MyKey is an identity column.

-- Create empty temp table
SELECT *
INTO #TmpMikeMike
FROM (SELECT
      m1.*
      FROM MyTable                 m1
          LEFT OUTER JOIN MyTable  m2 ON m1.MyKey=m2.MyKey
      WHERE 1=0
 ) dt

INSERT INTO #TmpMike
SELECT TOP 1 * FROM MyTable

SELECT * from #TmpMike

EDIT
THIS WORKS, with no errors...

-- Create empty temp table
SELECT *
INTO #Tmp_MyTable
FROM (SELECT
          m1.*
          FROM MyTable                 m1
              LEFT OUTER JOIN MyTable  m2 ON m1.KeyValue=m2.KeyValue
          WHERE 1=0
     ) dt
...
WHILE ...
BEGIN
    ...
    INSERT INTO #Tmp_MyTable
    SELECT TOP (@n) *
    FROM MyTable
    ...

END

however, what is your real problem? Why do you need to loop while inserting "*" into this temp table? You may be able to shift strategy and come up with a much better algorithm overall.