1. If the table does not have an auto-growing identity column (int) using the temp table
SELECT IDENTITY (int) NewID,.. Into #tmp from youtable
2. Tables with identity columns using table variables
INSERT into @tmpTableSELECT ..., NewID= (SELECT COUNT (*) from youtable b WHERE b.aid<=a.aid) from Youtable a
When you have a continuous newid from 1 to ~d, you can use loops to manipulate each record.
This newid you can do when it is pointer identification.
If you do not understand ~ ~ View the following stored procedures
/** * * * object:storedprocedure [dbo]. [AAA] Script date:04/24/2015 15:11:25 * * * * **/SET ansi_nulls ongoset quoted_identifier ongocreate PROCEDURE tablevariablesreplacecursor asdeclare @tmpTable table (NewIDint, oldidint, Oldname nvarchar ( $), Othername nvarchar ( $) Begin INSERT into @tmpTable SELECT NewID= (SELECT COUNT (*) from Cj_moneycenter b WHERE b.keyid<=A.keyid), KeyId, generalname,moneyname from yourtable a DECLARE @i int, @cnt INT SET @i=1SELECT @cnt=count (*) from yourtable while @i<=@cnt BEGIN SELECT* FROM @tmpTable WHERE newid=@i SET @i[Email protected]+1End End
SQL temp table or table variable substitution cursor