Copy data from source db to target db avoiding tables with specific field names (used for MIP COFA conversion work)

/*
 RUN THIS SCRIPT FROM THE *TARGET* DATABASE
 Copies table data from the @sourceDb database to the target database.
 Only tables that *do not* have segment code columns are copied.
 Optionally deletes the target table data if @deleteTargetData is set to 1
 */

 use [MyTargetDatabaseHere];
 declare @sourceDb nvarchar(255) = 'MySourceDatabaseHere';
 declare @deleteTargetData bit = 1;
 declare @message nvarchar(max) = null;

 begin try
         --disable triggers and constraints for target db
         exec sp_MSforeachtable 'alter table ? disable trigger all;
         alter table ? nocheck constraint all;'

         --create results temp table
         if (object_id('tempdb..#Results') is not null) drop table #Results
         create table #Results ([Name] nvarchar(1000), [Success] bit, [Error] nvarchar(max));

         --create #Table that contains the schema, table and column names of all tables in the database
         if (object_id('tempdb..#Tables') is not null) drop table #Tables

         select 
         [Table]
         ,[Schema]
         ,[Columns] = left([Columns],len(columns)-1) 
         ,[HasCodes] = convert(bit,case when charindex('sCodeID',[Columns], 1) > 0 then 1 else 0 end)
         ,[HasIdentity] = [Identity].[HasIdentity]
         into #Tables
         from
         (
                 select top 10000 
                 [Table] = t.name
                 ,[TableObjectId] = t.object_id
                 ,[Schema] = s.[Name]
                 ,[Columns] = substring(
                 (
                 select
                 '[' + c.name + '], '
                 from sys.columns c 
                 where t.object_id = c.object_id
                 order by c.[Name]
                 for xml path('')
                 )
                 ,1,8000)
                 from sys.tables t
                 join sys.schemas s on t.[schema_id]= s.[schema_id]
                 where t.type = 'U'
                 order by t.name
         ) as f1
         outer apply(select top 1 [HasIdentity] = [is_identity] from sys.columns where object_id = f1.[TableObjectId]) as [Identity]
         where f1.[Table] not like 'sys%'
         group by [Table],[Schema],[Columns],[HasIdentity]
         order by [Schema],[Table]

         
         --create #NoCodeTables from #Table that contains only those tables that do not contain segment code columns
         if (object_id('tempdb..#NoCodeTables') is not null) drop table #NoCodeTables

         select *
         into #NoCodeTables
         from #Tables
         where [HasCodes] = 0

         select * from #NoCodeTables

         declare @table nvarchar(255)
         declare @schema nvarchar(255)
         declare @schemaAndTable nvarchar(1000)
         declare @columns nvarchar(max)
         declare @hasIdentity bit
         declare @sql nvarchar(max)

         --iterate the #NoCodeTables rows and do the copying
         while(1=1)
         begin
                 set @table = null;
                 set @sql = null;
                 set @message = null;

                 --get a row to process from #NoCodeTables
                 select top 1
                 @table = [Table]
                 ,@schema = [Schema]
                 ,@columns = [Columns]
                 ,@hasIdentity = [HasIdentity]
                 ,@schemaAndTable = '[' + @schema + '].[' + @table + ']' --combine schema and table names here for convenience
                 from #NoCodeTables
                 order by [Table],[Schema]

                 if(@table is not null)
                 begin
                         begin try
                                 --optionally delete the target table data
                                 if(@deleteTargetData = 1)
                                 begin
                                         --if a truncate doesn't work then fall back to a delete
                                         set @message = @schemaAndTable + ' emptying...'
                                         raiserror (@message, 0, 1) with nowait --print immediately

                                         set @sql = 'begin try
                                                 truncate table ' + @schemaAndTable + ';
                                         end try
                                         begin catch
                                                 delete ' + @schemaAndTable + ';
                                         end catch';

                                         exec (@sql);
                                 end

                                 --copy from source to target table
                                 set @message = @schemaAndTable + ' copying...'
                                 raiserror (@message, 0, 1) with nowait --print immediately

                                 --some tables have identity columns, if so, set to allow copying first
                                 set @sql = 
                                         case when @hasIdentity = 1 then 'SET IDENTITY_INSERT [' + @table + '] ON;' else '' end +
                                         'insert ' + @schemaAndTable + ' (' + @columns + ') select ' + @columns + ' from [' + @sourceDb + '].' + @schemaAndTable + ';' + 
                                         case when @hasIdentity = 1 then 'SET IDENTITY_INSERT [' + @table + '] OFF;' else '' end
                         
                                 --print @sql
                                 exec(@sql);

                                 --success! set messages
                                 set @message = @schemaAndTable + ' done.'
                                 insert #Results ([Name],[Success]) values (@schemaAndTable, 1) --insert
                                 raiserror (@message, 0, 1) with nowait --print immediately
                         end try
                         begin catch
                                 --fail. set message
                                 set @message = @schemaAndTable + ' copy FAILED.' + error_message()
                                 insert #Results ([Name],[Success],[Error]) values (@schemaAndTable, 0, @message)
                                 raiserror (@message, 0, 1) with nowait --print immediately
                         end catch

                         --delete the completed row from #NoCodeTables in preparation to move on to the next one
                         delete from #NoCodeTables where [Table] = @table        
                 end
                 else break; --all #NoCodeTables rows have been processed, break out of the loop
         end

         --display results
         select * 
         from #Results
         order by [Name]

 end try
 begin catch
         --error of some kind, print it and re-enable triggers and constraints (which may fail if a partial copy situation)
         set @message = error_message()
         raiserror (@message, 0, 1) with nowait --print immediately

         --enable triggers and constraints for target db
         exec sp_MSforeachtable 'alter table ? enable trigger all;
         alter table ? check constraint all;'

 end catch

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

RUN THIS SCRIPT FROM THE *TARGET* DATABASE

Copies table data from the @sourceDb database to the target database.

Only tables that *do not* have segment code columns are copied.

Optionally deletes the target table data if @deleteTargetData is set to 1

use [MyTargetDatabaseHere];

declare @sourceDb nvarchar(255) = 'MySourceDatabaseHere';

declare @deleteTargetData bit = 1;

declare @message nvarchar(max) = null;

begin try

--disable triggers and constraints for target db

exec sp_MSforeachtable 'alter table ? disable trigger all;

alter table ? nocheck constraint all;'

--create results temp table

if (object_id('tempdb..#Results') is not null) drop table #Results

create table #Results ([Name] nvarchar(1000), [Success] bit, [Error] nvarchar(max));

--create #Table that contains the schema, table and column names of all tables in the database

if (object_id('tempdb..#Tables') is not null) drop table #Tables

select

[Table]

,[Schema]

,[Columns] = left([Columns],len(columns)-1)

,[HasCodes] = convert(bit,case when charindex('sCodeID',[Columns], 1) > 0 then 1 else 0 end)

,[HasIdentity] = [Identity].[HasIdentity]

into #Tables

from

(

select top 10000

[Table] = t.name

,[TableObjectId] = t.object_id

,[Schema] = s.[Name]

,[Columns] = substring(

(

select

'[' + c.name + '], '

from sys.columns c

where t.object_id = c.object_id

order by c.[Name]

for xml path('')

)

,1,8000)

from sys.tables t

join sys.schemas s on t.[schema_id]= s.[schema_id]

where t.type = 'U'

order by t.name

) as f1

outer apply(select top 1 [HasIdentity] = [is_identity] from sys.columns where object_id = f1.[TableObjectId]) as [Identity]

where f1.[Table] not like 'sys%'

group by [Table],[Schema],[Columns],[HasIdentity]

order by [Schema],[Table]

--create #NoCodeTables from #Table that contains only those tables that do not contain segment code columns

if (object_id('tempdb..#NoCodeTables') is not null) drop table #NoCodeTables

select *

into #NoCodeTables

from #Tables

where [HasCodes] = 0

select * from #NoCodeTables

declare @table nvarchar(255)

declare @schema nvarchar(255)

declare @schemaAndTable nvarchar(1000)

declare @columns nvarchar(max)

declare @hasIdentity bit

declare @sql nvarchar(max)

--iterate the #NoCodeTables rows and do the copying

while(1=1)

begin

set @table = null;

set @sql = null;

set @message = null;

--get a row to process from #NoCodeTables

select top 1

@table = [Table]

,@schema = [Schema]

,@columns = [Columns]

,@hasIdentity = [HasIdentity]

,@schemaAndTable = '[' + @schema + '].[' + @table + ']' --combine schema and table names here for convenience

from #NoCodeTables

order by [Table],[Schema]

if(@table is not null)

begin

begin try

--optionally delete the target table data

if(@deleteTargetData = 1)

begin

--if a truncate doesn't work then fall back to a delete

set @message = @schemaAndTable + ' emptying...'

raiserror (@message, 0, 1) with nowait --print immediately

set @sql = 'begin try

truncate table ' + @schemaAndTable + ';

end try

begin catch

delete ' + @schemaAndTable + ';

end catch';

exec (@sql);

end

--copy from source to target table

set @message = @schemaAndTable + ' copying...'

raiserror (@message, 0, 1) with nowait --print immediately

--some tables have identity columns, if so, set to allow copying first

set @sql =

case when @hasIdentity = 1 then 'SET IDENTITY_INSERT [' + @table + '] ON;' else '' end +

'insert ' + @schemaAndTable + ' (' + @columns + ') select ' + @columns + ' from [' + @sourceDb + '].' + @schemaAndTable + ';' +

case when @hasIdentity = 1 then 'SET IDENTITY_INSERT [' + @table + '] OFF;' else '' end

--print @sql

exec(@sql);

--success! set messages

set @message = @schemaAndTable + ' done.'

insert #Results ([Name],[Success]) values (@schemaAndTable, 1) --insert

raiserror (@message, 0, 1) with nowait --print immediately

end try

begin catch

--fail. set message

set @message = @schemaAndTable + ' copy FAILED.' + error_message()

insert #Results ([Name],[Success],[Error]) values (@schemaAndTable, 0, @message)

raiserror (@message, 0, 1) with nowait --print immediately

end catch

--delete the completed row from #NoCodeTables in preparation to move on to the next one

delete from #NoCodeTables where [Table] = @table

end

else break; --all #NoCodeTables rows have been processed, break out of the loop

end

--display results

select *

from #Results

order by [Name]

end try

begin catch

--error of some kind, print it and re-enable triggers and constraints (which may fail if a partial copy situation)

set @message = error_message()

raiserror (@message, 0, 1) with nowait --print immediately

--enable triggers and constraints for target db

exec sp_MSforeachtable 'alter table ? enable trigger all;

alter table ? check constraint all;'

end catch