Moving values in code tables

There are many times when I need to move data in code tables from one database server to another. Instead of going through the motions of creating a query and an export it’s much easier to move the data using scripts. Below is what I use, it prints out one big script that you can copy and paste and run.

set nocount on;

declare @CODETABLENAME nvarchar(100) = '',
@INCLUDEINACTIVE bit = 1;

declare @SQL nvarchar(max) = N'',
@CODETABLEADDPROC nvarchar(200) = 'USP_' + @CODETABLENAME + '_CREATEENTRY';

if object_id('tempdb..#CODETABLEVALUES') is not null
begin
drop table #CODETABLEVALUES
end

create table #CODETABLEVALUES(ID uniqueidentifier default newid() primary key, [DESCRIPTION] nvarchar(100), ACTIVE bit, PROCESSED bit default 0);

set @SQL = 'insert into #CODETABLEVALUES([DESCRIPTION], ACTIVE)';
set @SQL += char(10);
set @SQL += 'select DESCRIPTION, ACTIVE from ' + @CODETABLENAME;

execute sp_executesql @SQL;

print 'begin try';
print ' begin transaction';
print '';
print ' declare @CHANGEAGENTID uniqueidentifier;';
print '';
print ' exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;';
print '';

while exists(select 1 from #CODETABLEVALUES where PROCESSED = 0)
begin
set @SQL = '';

declare @ID uniqueidentifier,
@DESCRIPTION nvarchar(100),
@ACTIVE bit;

select
top 1
@ID = ID,
@DESCRIPTION = replace([DESCRIPTION], '''', ''''''),
@ACTIVE = ACTIVE
from
#CODETABLEVALUES
where
PROCESSED = 0;

set @SQL += ' --inserting value ''' + @DESCRIPTION + ''' into ' + @CODETABLENAME;
set @SQL += char(10);
set @SQL += ' if not exists(select 1 from ' + @CODETABLENAME + ' where DESCRIPTION = ''' + @DESCRIPTION + ''')';
set @SQL += char(10);
set @SQL += ' begin'
set @SQL += char(10);
set @SQL += ' exec ' + @CODETABLEADDPROC + ' @DESCRIPTION = ''' + @DESCRIPTION + ''', @ACTIVE = ' + cast(@ACTIVE as nvarchar(1)) + ', @CHANGEAGENTID = @CHANGEAGENTID;';
set @SQL += char(10);
set @SQL += ' end'
set @SQL += char(10);

update
#CODETABLEVALUES
set
PROCESSED = 1
where
ID = @ID;

print @SQL;
end

print ' commit transaction;';
print 'end try';
print 'begin catch';
print ' rollback transaction;';
print ' exec USP_RAISE_ERROR;';
print 'end catch';

Posted in CRM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s