好记性,不如写博客
记录每天学到,以备不时之需
今天记录的 SQL Server 备份最近7天的数据的存储过程,希望能帮助到大家,谢谢!
CREATE PROCEDURE [dbo].[Pro_BackupTable]
@table nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare @weekday varchar(1)
declare @tablename varchar(50)
declare @sqlstr nvarchar(1000)
set @weekday =(SELECT Datepart(weekday, GETDATE()+ @@DateFirst - 1) )
set @tablename=@table+'_'+@weekday
if object_id(@tablename,N'U') is not null
begin
set @sqlstr=' drop table '+@tablename+' select * into '+@tablename+' from '+@table
--print '存在'
end
else
begin
--print '不存在'
set @sqlstr=' select * into '+@tablename+' from '+@table
end
--select @sqlstr
exec(@sqlstr)
END
GO