sqlserver 常用脚本

1.数据库中表的备份

SELECT * INTO DestTable FROM SourceTable

2.同表中的数据复制，例子:将2013年4月11日当日的数据复制一份(且日期字段增加1年)

insert into table
select dateadd(month,12,date),id,value from table
where date between '2013-04-11 00:00:01' and '2013-04-11 23:59:59'

3.同表中的数据更新，例子:将某一时间段中的数据替换成另一时间段中的数据(需要保证原时间段数据量大于目标时间段数据量)

alter procedure  test_proc 
as
begin 
  declare @date datetime
  declare @id int
  declare @value varchar(50)
  declare @date1 datetime
  declare @id1 int
  declare @value1 varchar(50)
  
  DECLARE cursor_dest_list cursor  
  FOR SELECT date,id,value FROM table_1 where date between '2013-04-11 14:00:00' and '2013-04-11 15:00:00'
  order by date asc 
  OPEN cursor_dest_list 
  
  declare cursor_source_list cursor
  FOR SELECT date,id,value FROM table_1 where date between '2013-04-11 19:00:00' and '2013-04-11 22:00:00'  
  order by date asc
  open cursor_source_list
              
  FETCH NEXT from cursor_dest_list into @date, @id, @value  
  WHILE @@FETCH_STATUS = 0     
  BEGIN
    FETCH NEXT from cursor_source_list into @date1, @id1, @value1  
    WHILE @@FETCH_STATUS = 0     
    BEGIN
      update table_1 set value = @value1  where date = @date
      break
    END
    FETCH NEXT from cursor_dest_list into @date, @id, @value   
  END
   
  CLOSE cursor_source_list               
  DEALLOCATE cursor_source_list
  CLOSE cursor_dest_list               
  DEALLOCATE cursor_dest_list
end