Monday, December 26, 2011

Insert Data into same Table from table with another column values

step 1:  first  we have to insert data into table
insert into emp select * from emp where id=2


step 2:  then create CTE like this in which we give row_number for each rows for identity of rows

SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE

WITH DuplicateRecords AS
(
SELECT *,row_number() OVER( ORDER BY name
)
AS RowNumber FROM emp where id=2
)

step 3 :  then we update our desired rows
update DuplicateRecords set id=31 WHERE RowNumber>1

No comments:

Post a Comment