I’m migrating a database from MySQL to SQL Server. In MySQL I have this bit of stored procedure:

Declare cidx int;

DROP TEMPORARY TABLE IF EXISTS coretmp1;

Create temporary table coretmp1 SELECT * FROM corebuild order by Idx desc limit 1;
select Idx+1 from coretmp1 into cidx;
UPDATE coretmp1 SET Idx = cidx, Piece=0;
INSERT INTO corebuild SELECT * FROM coretmp1;

DROP TEMPORARY TABLE IF EXISTS coretmp1;

Select cidx;

This way all fields get copied, even if the table is altered, and a new sequential key is generated, which is returned by the script. How can I do this in SQL Server as it does not have Temporary tables and I can’t seem to create a table variable from an existing table?