USP_BBNC_UPDATEMAPPINGTABLES
Updates tables that provide ID mapping for Blackbaud Internet Solutions.
Definition
Copy
CREATE procedure dbo.USP_BBNC_UPDATEMAPPINGTABLES
with execute as owner
as
set nocount on;
declare @RECORDSADDED int;
declare @STARTINGCOUNT int;
declare @ROWCOUNT int;
declare @TOTALROWS int;
declare @SQL nvarchar(max);
declare @MSG nvarchar(255);
declare @ID uniqueidentifier;
declare @TABLENAME nvarchar(100);
declare @CODETABLENAME nvarchar(100);
declare @PARAMS nvarchar(40);
declare @CODETABLES table
(
ROWNUM int identity (1, 1) primary key not null ,
ID uniqueidentifier,
TABLENAME nvarchar(100),
CODETABLENAME nvarchar(100)
) ;
set @RECORDSADDED = 0;
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', 'Creating country id records...', 1, null;
select @STARTINGCOUNT = count(*) from dbo.BBNCCODETABLEIDMAP;
insert into dbo.BBNCCODETABLEIDMAP (CODETABLECATALOGID, TABLEENTRYID)
select '6DCD65EA-4D60-4C43-BA04-63DFC0087212', ID from dbo.COUNTRY
where not ID in(select TABLEENTRYID from dbo.BBNCCODETABLEIDMAP
where CODETABLECATALOGID = '6DCD65EA-4D60-4C43-BA04-63DFC0087212');
select @RECORDSADDED = @RECORDSADDED + (select count(*) - @STARTINGCOUNT from dbo.BBNCCODETABLEIDMAP);
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', 'Creating state id records...', 1, null;
select @STARTINGCOUNT = count(*) from dbo.BBNCCODETABLEIDMAP;
insert into dbo.BBNCCODETABLEIDMAP (CODETABLECATALOGID, TABLEENTRYID)
select 'FE193125-9AFA-46B8-86BD-E0E1FAB4C32E', ID from dbo.STATE
where not ID in(select TABLEENTRYID from dbo.BBNCCODETABLEIDMAP
where CODETABLECATALOGID = 'FE193125-9AFA-46B8-86BD-E0E1FAB4C32E');
select @RECORDSADDED = @RECORDSADDED + (select count(*) - @STARTINGCOUNT from dbo.BBNCCODETABLEIDMAP);
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', 'Creating educational institution code id records...', 1, null;
select @STARTINGCOUNT = count(*) from dbo.BBNCCODETABLEIDMAP;
insert into dbo.BBNCCODETABLEIDMAP (CODETABLECATALOGID, TABLEENTRYID)
select '0E885882-A716-49B8-8F2F-A6BAE5D220D8', ID from dbo.EDUCATIONALINSTITUTION
where not ID in(select TABLEENTRYID from dbo.BBNCCODETABLEIDMAP
where CODETABLECATALOGID = '0E885882-A716-49B8-8F2F-A6BAE5D220D8');
select @RECORDSADDED = @RECORDSADDED + (select count(*) - @STARTINGCOUNT from dbo.BBNCCODETABLEIDMAP);
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', 'Creating designation id records...', 1, null;
select @STARTINGCOUNT = count(*) from dbo.BBNCDESIGNATIONIDMAP;
insert into dbo.BBNCDESIGNATIONIDMAP (DESIGNATIONID)
select ID from dbo.DESIGNATION where not ID in (select DESIGNATIONID from dbo.BBNCDESIGNATIONIDMAP);
select @RECORDSADDED = @RECORDSADDED + ( select count(*) - @STARTINGCOUNT from dbo.BBNCDESIGNATIONIDMAP);
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', 'Creating designation level id records...', 1, null;
select @STARTINGCOUNT = count(*) from dbo.BBNCDESIGNATIONLEVELIDMAP;
insert into dbo.BBNCDESIGNATIONLEVELIDMAP (DESIGNATIONLEVELID)
select ID from dbo.DESIGNATIONLEVEL where not ID in (select DESIGNATIONLEVELID from dbo.BBNCDESIGNATIONLEVELIDMAP);
select @RECORDSADDED = @RECORDSADDED + ( select count(*) - @STARTINGCOUNT from dbo.BBNCDESIGNATIONLEVELIDMAP);
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', 'Creating appeal id records...', 1, null;
select @STARTINGCOUNT = count(*) from dbo.BBNCAPPEALIDMAP;
insert into dbo.BBNCAPPEALIDMAP (APPEALID)
select ID from dbo.APPEAL where not ID in (select APPEALID from dbo.BBNCAPPEALIDMAP);
select @RECORDSADDED = @RECORDSADDED + ( select count(*) - @STARTINGCOUNT from dbo.BBNCAPPEALIDMAP);
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', 'Creating team fundraising team id records...', 1, null;
select @STARTINGCOUNT = count(*) from dbo.BBNCTEAMFUNDRAISINGTEAMIDMAP;
insert into dbo.BBNCTEAMFUNDRAISINGTEAMIDMAP (TEAMFUNDRAISINGTEAMID)
select ID from dbo.TEAMFUNDRAISINGTEAM where not ID in (select TEAMFUNDRAISINGTEAMID from dbo.BBNCTEAMFUNDRAISINGTEAMIDMAP);
select @RECORDSADDED = @RECORDSADDED + ( select count(*) - @STARTINGCOUNT from dbo.BBNCTEAMFUNDRAISINGTEAMIDMAP);
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', 'Creating team fundraiser id records...', 1, null;
select @STARTINGCOUNT = count(*) from dbo.BBNCTEAMFUNDRAISERIDMAP;
insert into dbo.BBNCTEAMFUNDRAISERIDMAP (TEAMFUNDRAISERID)
select ID from dbo.TEAMFUNDRAISER where not ID in (select TEAMFUNDRAISERID from dbo.BBNCTEAMFUNDRAISERIDMAP);
select @RECORDSADDED = @RECORDSADDED + ( select count(*) - @STARTINGCOUNT from dbo.BBNCTEAMFUNDRAISERIDMAP);
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', 'Creating event id records...', 1, null;
select @STARTINGCOUNT = count(*) from dbo.BBNCEVENTIDMAP;
insert into dbo.BBNCEVENTIDMAP (EVENTID)
select ID from dbo.EVENT where not ID in (select EVENTID from dbo.BBNCEVENTIDMAP);
select @RECORDSADDED = @RECORDSADDED + ( select count(*) - @STARTINGCOUNT from dbo.BBNCEVENTIDMAP);
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', 'Creating event price id records...', 1, null;
select @STARTINGCOUNT = count(*) from dbo.BBNCEVENTPRICEIDMAP;
insert into dbo.BBNCEVENTPRICEIDMAP (EVENTPRICEID)
select ID from dbo.EVENTPRICE where not ID in (select EVENTPRICEID from dbo.BBNCEVENTPRICEIDMAP);
select @RECORDSADDED = @RECORDSADDED + ( select count(*) - @STARTINGCOUNT from dbo.BBNCEVENTPRICEIDMAP);
-- Add id map rows for every code table currently defined in the system
insert into @CODETABLES (ID, TABLENAME, CODETABLENAME) select ID, DBTABLENAME, CODETABLENAME from dbo.CODETABLECATALOG;
select @TOTALROWS = count(*) from @CODETABLES;
set @PARAMS = N'@ID uniqueidentifier';
set @ROWCOUNT = 1;
select @STARTINGCOUNT = count(*) from dbo.BBNCCODETABLEIDMAP;
while @ROWCOUNT <= @TOTALROWS
begin
select
@ID = ID,
@TABLENAME = TABLENAME,
@CODETABLENAME = CODETABLENAME
from
@CODETABLES
where
ROWNUM = @ROWCOUNT;
set @MSG = N'Creating ' + @CODETABLENAME + N' records...';
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', @MSG, 1, null;
set @SQL = N'insert into dbo.BBNCCODETABLEIDMAP (CODETABLECATALOGID, TABLEENTRYID) ';
set @SQL = @SQL + N'select @ID, ID from dbo.' + @TABLENAME;
set @SQL = @SQL + N' where not ID in(select TABLEENTRYID from dbo.BBNCCODETABLEIDMAP ';
set @SQL = @SQL + N' where CODETABLECATALOGID = @ID)';
exec sp_executesql @SQL, @PARAMS, @ID = @ID;
set @ROWCOUNT = @ROWCOUNT + 1;
end;
select @RECORDSADDED = @RECORDSADDED + (select count(*) - @STARTINGCOUNT from dbo.BBNCCODETABLEIDMAP);
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', 'Creating selection id records...', 1, null;
select @STARTINGCOUNT = count(*) from dbo.BBNCIDSETIDMAP;
insert into dbo.BBNCIDSETIDMAP (IDSETREGISTERID)
select ID from dbo.IDSETREGISTER where not ID in(select IDSETREGISTERID from dbo.BBNCIDSETIDMAP);
select @RECORDSADDED = @RECORDSADDED + (select count(*) - @STARTINGCOUNT from dbo.BBNCIDSETIDMAP);
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', 'Creating code table catalog id records...', 1, null;
select @STARTINGCOUNT = count(*) from dbo.BBNCCODETABLECATALOGIDMAP;
insert into dbo.BBNCCODETABLECATALOGIDMAP (CODETABLECATALOGID)
select ID from dbo.CODETABLECATALOG where not ID in(select CODETABLECATALOGID from dbo.BBNCCODETABLECATALOGIDMAP);
select @RECORDSADDED = @RECORDSADDED + (select count(*) - @STARTINGCOUNT from dbo.BBNCCODETABLECATALOGIDMAP);
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', 'Creating attribute category id records...', 1, null;
select @STARTINGCOUNT = count(*) from dbo.BBNCATTRIBUTECATEGORYIDMAP;
insert into dbo.BBNCATTRIBUTECATEGORYIDMAP (ATTRIBUTECATEGORYID)
select ID from dbo.ATTRIBUTECATEGORY where not ID in(select ATTRIBUTECATEGORYID from dbo.BBNCATTRIBUTECATEGORYIDMAP);
select @RECORDSADDED = @RECORDSADDED + (select count(*) - @STARTINGCOUNT from dbo.BBNCATTRIBUTECATEGORYIDMAP);
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', 'Creating revenue id records...', 1, null;
select @STARTINGCOUNT = count(*) from dbo.BBNCREVENUEIDMAP;
insert into dbo.BBNCREVENUEIDMAP (REVENUEID)
select ID from dbo.REVENUE where not ID in(select REVENUEID from dbo.BBNCREVENUEIDMAP);
select @RECORDSADDED = @RECORDSADDED + (select count(*) - @STARTINGCOUNT from dbo.BBNCREVENUEIDMAP);
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', 'Creating revenue split id records...', 1, null;
select @STARTINGCOUNT = count(*) from dbo.BBNCREVENUESPLITIDMAP;
insert into dbo.BBNCREVENUESPLITIDMAP (REVENUESPLITID)
select ID from dbo.REVENUESPLIT where not ID in(select REVENUESPLITID from dbo.BBNCREVENUESPLITIDMAP);
select @RECORDSADDED = @RECORDSADDED + (select count(*) - @STARTINGCOUNT from dbo.BBNCREVENUESPLITIDMAP);
-- Changed soft credits to recognitions
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', 'Creating revenue recognition id records...', 1, null;
select @STARTINGCOUNT = count(*) from dbo.BBNCREVENUERECOGNITIONIDMAP;
insert into dbo.BBNCREVENUERECOGNITIONIDMAP (REVENUERECOGNITIONID)
select ID from dbo.REVENUERECOGNITION where not ID in(select REVENUERECOGNITIONID from dbo.BBNCREVENUERECOGNITIONIDMAP);
select @RECORDSADDED = @RECORDSADDED + (select count(*) - @STARTINGCOUNT from dbo.BBNCREVENUERECOGNITIONIDMAP);
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', 'Creating relationship id records...', 1, null;
select @STARTINGCOUNT = count(*) from dbo.BBNCRELATIONSHIPIDMAP;
insert into dbo.BBNCRELATIONSHIPIDMAP (RELATIONSHIPID)
select ID from dbo.RELATIONSHIP where not ID in(select RELATIONSHIPID from dbo.BBNCRELATIONSHIPIDMAP);
select @RECORDSADDED = @RECORDSADDED + (select count(*) - @STARTINGCOUNT from dbo.BBNCRELATIONSHIPIDMAP);
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', 'Creating educational history id records...', 1, null;
select @STARTINGCOUNT = count(*) from dbo.BBNCEDUCATIONALHISTORYIDMAP;
insert into dbo.BBNCEDUCATIONALHISTORYIDMAP (EDUCATIONALHISTORYID)
select ID from dbo.EDUCATIONALHISTORY where not ID in(select EDUCATIONALHISTORYID from dbo.BBNCEDUCATIONALHISTORYIDMAP);
select @RECORDSADDED = @RECORDSADDED + (select count(*) - @STARTINGCOUNT from dbo.BBNCEDUCATIONALHISTORYIDMAP);
exec dbo.USP_BUSINESSPROCESSSTATUS_UPDATESTATUS '3D81E522-B121-4ABC-9066-1FC856A90BEE', 'Creating tribute id records...', 1, null;
select @STARTINGCOUNT = count(*) from dbo.BBNCTRIBUTEIDMAP;
insert into dbo.BBNCTRIBUTEIDMAP (TRIBUTEID)
select ID from dbo.TRIBUTE where not ID in (select TRIBUTEID from dbo.BBNCTRIBUTEIDMAP);
select @RECORDSADDED = @RECORDSADDED + ( select count(*) - @STARTINGCOUNT from dbo.BBNCTRIBUTEIDMAP);
select @RECORDSADDED as RECORDSADDED;