数据库自动抽取-SQL

背景：MSSQL 2012

功能：

1、新建B数据库

2、从A数据库 自动查询多个表中指定条件的数据，并保存到B数据库

3、自动将B数据库分离

代码如下：

 -- 创建数据库
CREATE DATABASE [SmallNozzleTest-Temp]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'SmallNozzleTest-Temp', FILENAME = N'D:\SmallNozzleTest-Temp.mdf' , SIZE = 20480KB , MAXSIZE = UNLIMITED, FILEGROWTH = 20480KB )
 LOG ON 
( NAME = N'SmallNozzleTest-Temp_log', FILENAME = N'D:\SmallNozzleTest-Temp_log.ldf' , SIZE = 20480KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO

--查询数据到并保存到此数据库 
 declare @i int;
 declare @max int;
 declare @table_run varchar(60);
 declare @table_save varchar(60);
 declare @str varchar(200);

 set @i=1;
 set @max=35;
 

 while @i <@max
 begin
 set @table_run=  '[SmallNozzleTest].[dbo].[loop_'+CONVERT(varchar(5),@i)+']'; 
 set @table_save= '[SmallNozzleTest-Temp].[dbo].[loop_'+CONVERT(varchar(5),@i)+']'; 
	  
	  IF EXISTS(SELECT * FROM sysobjects WHERE id=object_id(@table_save) AND OBJECTPROPERTY(id,'IsUserTable') = 1) --表是否存在
	  begin
	exec( ' drop table '+ @table_save);
	end

 set @str=' select * into '+ @table_save+'  from '+ @table_run +
        '  where collectTime like ''2018-08-2%'' or collectTime like ''2018-09-30'' ' ;
 
 exec(@str); 
 set @i=@i+1;
 end
 
 
--分离此数据库
 
use master
go 
exec sp_detach_db 'SmallNozzleTest-Temp'
go