我想将一张桌子的价值复制到两张桌子 [英] i want to copy value of one table into two table

问题描述

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

如果在sql中运行此sp，则会出现类似错误，只有在使用列列表且IDENTITY_INSERT为ON时才能指定.

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.

推荐答案

通过下面的2个链接

此处 [ http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57953 [ ^ ]

谢谢
--RA

go through the below 2 links

Here[^]

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57953[^]

Thanks
--RA

您将不得不在insert语句中提及该列，并为其插入值.

问候

You''ll have to mention that column in the insert statement and insert value for that as well.

Regards

这篇关于我想将一张桌子的价值复制到两张桌子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！