How to update Identity Column from ROW_NUMBER in sql server

Question

Hello How to update Identity Column from ROW_NUMBER in sql server 2008? I am using this query for update but it's getting error:- Cannot update identity column 'SNO' set IDENTITY_INSERT tableSalesQuotation ON update tableSalesQuotation set SNO=SubQuery.SNO from (SELECT Row_Number() OVER (ORDER BY SNO) as SNO FROM tableSalesQuotation ) SubQuery set IDENTITY_INSERT tableSalesQuotation OFF please help me. Thanks in Advance. Ankit Agarwal Software Engineer

Jignesh Trivedi · Answer

Hi As per my knowledge it is not possible to update value of Identity column. IDENTITY_INSERT is only work with Insert into statement.... but there is one work around try create table tableSalesQuotation ( Id int Identity(11) Name varchar(20) ) Insert into tableSalesQuotation values('Tejas') Insert into tableSalesQuotation values('Jignesh') Insert into tableSalesQuotation values('Rakesh') select * from tableSalesQuotation select * into #dummy from tableSalesQuotation delete from tableSalesQuotation set IDENTITY_INSERT tableSalesQuotation ON Insert Into tableSalesQuotation (IdName) SELECT Row_Number() OVER (ORDER BY Name) as Idname FROM #dummy set IDENTITY_INSERT tableSalesQuotation OFF hope this will help you.

JobPencil · Answer

Try This Link http://stackoverflow.com/questions/19155775/how-to-update-identity-column-in-sql-server Thanks Find Jobs