-->try
declare @x int,@str varchar(20),@sql varchar(max)
set @x=0
while @x<20
begin
    set @x=@x+1
    set @str='按年分层 >'+cast(@x-1 as varchar(2))+'& <='+cast(@x as varchar(2))+'表'
    set @sql=isnull(@sql,'')+' select * into [数据库2].dbo.'+@str+' from
    (
    select *,status status2,[YEAR]-'+cast((@x-1) as varchar(5))+' year2 FROM RECORDCD4
    WHERE [YEAR]>'+cast((@x-1) as varchar(5))+' and [YEAR]<='+cast(@x as varchar(5))+'
    UNION
    select * ,0,1 FROM RECORDCD4
    WHERE [YEAR]>'+cast(@x as varchar(5))+'
    ) y'
end 
exec(@sql)

------解决方案--------------------
楼上正解

------解决方案--------------------

SQL code
declare @x int,@str varchar(20),@sql VARCHAR(max)
set @x=0
while @x<20
begin
    set @x=@x+1
    set @str='[按年分层 >'+cast(@x-1 as varchar(2))+'& <='+cast(@x as varchar(2))+'表]'
    SET @sql='select * into [数据库2].dbo.'+@str+' from
    (
    select *,status status2,[YEAR]-('+LTRIM(@x-1)+') year2 FROM RECORDCD4
    WHERE [YEAR]>'+LTRIM(@x-1)+' and [YEAR]<='+LTRIM(@x)+'
    UNION
    select * ,0,1 FROM RECORDCD4
    WHERE [YEAR]>'+LTRIM(@x)+'
    ) y'
    PRINT @sql
    --EXEC (@sql)
END