--循环创建年备份表
use mcMonitor
declare @year int
declare @myear varchar(4)
set @year = 2018
begin
while @year < 2023
begin
declare @backupName varchar(50)
set @backupName='TmpDailyProduction'+ (convert(varchar(4),@year))
set @myear = convert(varchar(4),@year)
print 'Start year of: ' + convert(varchar(4),@year)
declare @sql varchar(5000)
set @sql = 'SELECT * into '+@backupName+' FROM [dbo].[TmpDailyProduction] '+
'where year(TxnDate) = convert(int,'+@myear+')'
print @sql
exec(@sql)
set @year = @year + 1
print @year
end
end