/****** Object: StoredPRocedure [dbo].[usp_CopyDataBase_Data] Script Date: 02/12/2017 21:45:12 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_CopyDataBase_Data]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[usp_CopyDataBase_Data]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_CopyDataBase_Data]') AND type in (N'P', N'PC'))BEGINEXEC dbo.sp_executesql @statement = N'-- =============================================-- Author: -- Create date: -- Description: -- EXEC [usp_CopyDataBase_Data] ''IPVA_Apple_2'',''IPVA_Apple''-- =============================================CREATE PROCEDURE [dbo].[usp_CopyDataBase_Data] @DataBaseA nvarchar(50),@DataBaseB nvarchar(50)ASBEGINSET NOCOUNT ON;declare @a int,@b int,@c intdeclare @count_a int,@count_b int,@count_c intdeclare @Name_A nvarchar(50),@Name_AA nvarchar(50),@Name_B nvarchar(50),@Name_C nvarchar(50)declare @SQL nvarchar(max)DECLARE @pkStr NVARCHAR(max)DECLARE @colStr NVARCHAR(max)DECLARE @insertStr NVARCHAR(max)create table #TableNameA(ID INT IDENTITY(1,1),table_name nvarchar(50),fk_name nvarchar(50),reference_table_name nvarchar(50),fk_list_number int)create table #TableNameB(ID INT IDENTITY(1,1),Name nvarchar(50))create table #TableNameC(ID INT IDENTITY(1,1),Name nvarchar(50))insert into #TableNameASELECT table_name,fk_name,reference_table_name,fk_list_numberFROM (SELECT object_name(f.object_id) AS fk_name,object_name(f.parent_object_id) AS table_name,object_name(f.referenced_object_id) AS reference_table_name,k.parent_column_id AS fk_list_numberFROM sys.foreign_keys fINNER JOIN sys.foreign_key_columns kon f.object_id = k.constraint_object_id and f.referenced_object_id = k.referenced_object_id) t order by fk_list_number descselect @count_a=COUNT(*) from #TableNameAset @a=1while(@a<=@count_a)beginselect @Name_A=reference_table_name from #TableNameA where ID=@aif not exists(select * from #TableNameB where Name =@Name_A)begininsert into #TableNameB select @Name_Aendset @a=@a+1endset @a=1while(@a<=@count_a)beginselect @Name_AA=table_name from #TableNameA where ID=@aif not exists(select * from #TableNameB where Name =@Name_AA)begininsert into #TableNameB select @Name_AAendset @a=@a+1endinsert into #TableNameC select name from sysobjects where xtype=''U'' select @count_b=COUNT(*) from #TableNameCset @b=1while(@b<=@count_b)beginselect @Name_B=Name from #TableNameC where ID=@bif not exists(select * from #TableNameB where Name = @Name_B)begininsert into #TableNameB select @Name_Bendset @b=@b+1endselect @count_c=COUNT(*) from #TableNameBset @c=1while(@c<=@count_c)beginselect @Name_C=Name from #TableNameB where ID=@cif(@Name_C =''Summary_Thirty'' or @Name_C =''Summary_Sixty'' or @Name_C =''Summary_Day'' or @Name_C =''Summary_Week'' or @Name_C =''Summary_Month'' or @Name_C =''Summary_Year'' or @Name_C =''Traffic_CountData'')beginprint 1endelsebeginif exists(select * from syscolumns where id=object_id(N''[''+@Name_C+'']'') and COLUMNPROPERTY(id,name,''IsIdentity'')=1)begin--主鍵EXEC [usp_SUBDB_GetPrimaryKeyofTableScript] @Name_C,1,@pkStr output--列對等EXEC [usp_SUBDB_GetPrimaryKeyofTableScript] @Name_C,0,@colStr output--插入EXEC [usp_SUBDB_GetPrimaryKeyofTableScript] @Name_C,2,@insertStr outputset @SQl='' SET IDENTITY_INSERT [''+@DataBaseB+''].[dbo].[''+@Name_C+''] ON merge [''+@DataBaseB+''].[dbo].[''+@Name_C+''] a --要更新的目標表using ( select * from [''+@DataBaseA+''].[dbo].[''+@Name_C+''])b --源表on ''+@pkStr+'' --更新條件（即主鍵）when matched --如果主鍵匹配，更新then update set ''+@colStr+'' when not matchedthen ''+@insertStr+''; SET IDENTITY_INSERT [''+@DataBaseB+''].[dbo].[''+@Name_C+''] off ''--print @SQlexec (@SQl)endelsebeginset @SQL='' insert into [''+@DataBaseB+''].[dbo].[''+@Name_C+''] select * from [''+@DataBaseA+''].[dbo].[''+@Name_C+''] ''--print @SQlexec (@SQl)endendset @c=@c+1enddrop table #TableNameAdrop table #TableNameBdrop table #TableNameCEND' ENDGO
