i want to copy value of one table into two table

See more:

SQL

server

create proc sp_backup
@fromdate date,
@todate date

as
begin
if not exists(select * from sys.objects where type_desc='USER_TABLE' and name='Process_Trn_backup' )
begin

insert into Process_Trn_backup select * from Process_Trn   where  Process_Date  between   @fromdate   and @todate

set identity_insert Process_Trn_backup on

delete from Process_Trn   where  Process_Date  between   @fromdate   and @todate

alter table Process_Trn_backup add  archived0n datetime ,archivedby varchar(150)

update Process_Trn_backup set archived0n =GETDATE () ,archivedby='Rajesh' where Process_Date  between   @fromdate   and @todate


end
else
begin

--update syscolumns set colstat = 0 --turn off bit 1 which indicates identity column
--where id = object_id('Process_Trn_backup') and name = 'column_name'
--go
set identity_insert Process_Trn_backup on



insert into Process_Trn_backup(Process_Trn_Pk,User_Fk,Process_Date,Login_Time,Logout_Time,Core_Time,Noncore_time,Zone_Name,archived0n,archivedby) select * ,'','' from Process_Trn   where  Process_Date  between   @fromdate   and @todate

 set identity_insert Process_Trn_backup off

delete from Process_Trn   where  Process_Date  between   @fromdate   and @todate
update Process_Trn_backup set archived0n =GETDATE () ,archivedby='Rajesh' where Process_Date  between   @fromdate   and @todate



end

end

if run this sp in sql i got error like can only be specified when a column list is used and IDENTITY_INSERT is ON.