数据表中数据迁移存储过程

Create Proc procDoc_binary_backupByAllCoInsertAndSelect
@BeginTime varchar(50),
@EndTime varchar(50)
AS
Begin
set nocount on--不返回受影响条数
declare @StrSql nvarchar(500),@CreateSQL nvarchar(500),@Count int,@InsideCount int
Set @StrSql=N'Select @InsideCount=Count(1) From SysObjects Where Id = object_id(''DocBinaryBackup'') And Type = ''U'''

Exec Sp_ExecuteSQL @StrSql,N'@InsideCount int output',@Count output

IF(@Count = 0)--判断备份表是否存在，如果不存在则创建
Begin
Set @CreateSQL='Create Table DocBinaryBackup
(
[id] [int] IDENTITY(1,1) primary key,
[template_type] [varchar](50) NULL,
[template_date] [datetime] NULL,
[template_content] [image] NULL,
[todoId] [int] NULL,
[userId] [int] NULL,
[Dint1] [int] NULL,
[Dint2] [int] NULL,
[Dtext1] [varchar](50) NULL,
[Dtext2] [varchar](50) NULL)'

Exec(@CreateSQL)
End
--向备份表中导入指定时间段数据

Set @StrSql=N'set identity_insert DocBinaryBackup on

insert into DocBinaryBackup([id],[template_type],[template_date],[template_content],[todoId],[userId],[Dint1],[Dint2],[Dtext1],[Dtext2])select [id],[template_type],[template_date],[template_content],[todoId],[userId],[Dint1],[Dint2],[Dtext1],[Dtext2] from Doc_template where DATEDIFF(d,[template_date],'''+@EndTime+''')>=0'
Exec(@StrSql)
End
--如果备份数据导出完毕，则删除原表数据
if @@ERROR=0
begin
set @StrSql=N'delete Doc_template where DATEDIFF(d,[template_date],'''+@EndTime+''')>=0'

Exec(@StrSql)
end
----判断执行过程中是否出错
if @@ERROR=0
return 1
else
return 0

转载于:https://www.cnblogs.com/fourw/p/3429953.html