背景: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