--我要删除里面2005年全年的数据,
CREATE PROC P_SYSTEM_DelAllUserTableData
as
Begin
declare @name varchar(20)
Declare Cur Cursor For
select Name from sysobjects where xtype='u' and status>=0
declare @SQL Varchar(20)
Open Cur
Fetch Cur Into @name
While @@FETCH_STATUS=0
BEGIN
Set @sql='DELETE '+@name+' where year(时间字段)=''2005'''
Exec(@sql)
Fetch Cur Into @name
End
Close Cur
Deallocate cur
End
GO
--然后实现备份和还原即可
CREATE PROC P_SYSTEM_DelAllUserTableData
as
Begin
declare @name varchar(20)
Declare Cur Cursor For
select Name from sysobjects where xtype='u' and status>=0
declare @SQL Varchar(20)
Open Cur
Fetch Cur Into @name
While @@FETCH_STATUS=0
BEGIN
Set @sql='DELETE '+@name+' where year(时间字段)=''2005'''
Exec(@sql)
Fetch Cur Into @name
End
Close Cur
Deallocate cur
End
GO
--然后实现备份和还原即可
@command1 = 'delete ? where [year]=2005',
@whereand = 'and exists(select * from syscolumns where name = ''year'' and id = o.id)' -- 有的表没有year列, 则不需要删除数据. 所以有这样的条件限制.
2 逐表删除2005年数据 (sp_msforeachtable)
3 将剩余2006年数据DTS到新机
表f_charge内有字段rec_datetime,2005-05-25 10:16:57.000
表f_shift内有字段off_datetime,2005-07-08 21:50:53.000
不知道这种办法行不行?
select * into aa from f_charge where rec_datetime>=cast('2005-01-01 00:00:00' as datetime)
truncate table f_charge
insert into f_charge select * from aaselect * into ab from f_shift where off_datetime>=cast('2005-01-01 00:00:00' as datetime)
truncate table f_shift
insert into f_shift select * from ab
不知道有没有更好的办法?
备份方面,我想先做完备份以后,删除2005年的数据。再备份一次,就只有2006年的数据了,然后把文件拷贝到新的机器上,做还原应该就可以了。不知道这种方法是不是最好的?