Особенности объединения БД

План:

Рассказать о проблемах при объединении: потерянные записи в подчиненных таблицах, пропуски в значениях ключевых полей, необходимость реиндексации сливаемых данных, необходимость сохранения ссылочной целостности в сливаемых данных, необходимость в справочниках исключать дублирующие записи. Привести пример в виде заполненных таблиц и все проблемы показать.

--Пример объединения данных нескольктх кусков БД с исключением дублей и реиндексацией

--создание тестовой БД с данными для объединения и дубликатами в справочниках

/*

use master

drop database db_teachers

create database db_teachers

use db_teachers

create table sppost(idpost int primary key, postname char(30))

create table spteachers(idteacher int primary key identity (1,1),

idpost int Foreign key references sppost(idpost) On update cascade, FIO char(30))

insert into sppost(idpost, postname)Values(20, 'матем')

insert into sppost(idpost, postname)Values(10, 'физика')

insert into sppost(idpost, postname)Values(30, 'литер')

insert into sppost(idpost, postname)Values(15, 'черчение')

insert into sppost(idpost, postname)Values(16, 'механика')

insert into sppost(idpost, postname)Values(17, 'БД')

insert into spteachers(idpost, FIO)Values(20, 'Иванов')

insert into spteachers(idpost, FIO)Values(10, 'Петров')

insert into spteachers(idpost, FIO)Values(10, 'Сидоров')

insert into spteachers(idpost, FIO)Values(17, 'Федоров')

insert into spteachers(idpost, FIO)Values(16, 'Капин')

insert into spteachers(idpost, FIO)Values(16, 'Сорин')

create table sppost1(idpost int primary key, postname char(30))

create table spteachers1(idteacher int primary key identity (1,1),

idpost int Foreign key references sppost1(idpost) On update cascade, FIO char(30))

insert into sppost1(idpost, postname)Values(2, 'математика')

insert into sppost1(idpost, postname)Values(1, 'физика твердого тела')

insert into sppost1(idpost, postname)Values(3, 'литература')

insert into sppost1(idpost, postname)Values(4, 'черчение')

insert into sppost1(idpost, postname)Values(16, 'механика')

insert into sppost1(idpost, postname)Values(17, 'БД')

insert into spteachers1(idpost, FIO)Values(2, 'Иванов')

insert into spteachers1(idpost, FIO)Values(1, 'Петров')

insert into spteachers1(idpost, FIO)Values(1, 'Сидоров')

insert into spteachers1(idpost, FIO)Values(17, 'Федоров')

insert into spteachers1(idpost, FIO)Values(16, 'Капин')

insert into spteachers1(idpost, FIO)Values(16, 'Сорин')

*/

--select * from sppost union select * from sppost1

use db_teachers

--реиндексация 1 части

Declare @temp1 table(idpost int)

insert into @temp1 select idpost from sppost order by idpost asc

declare @i int, @id int

set @i = 0

declare temp1 scroll cursor for (select idpost from @temp1)

open temp1

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM temp1 into @id

set @i = @i+1

update sppost set idpost = @i where idpost = @id

END

close temp1

DEALLOCATE temp1

--реиндексация 2 части

Declare @temp2 table(idpost int)

insert into @temp2 select idpost from sppost1 order by idpost asc

declare temp2 scroll cursor for (select idpost from @temp2)

open temp2

set @i = ISNULL((select max(idpost) from sppost),0)

FETCH NEXT FROM temp2 into @id

set @i = @i+1

update sppost1 set idpost = @i where idpost = @id

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM temp2 into @id

set @i = @i+1

update sppost1 set idpost = @i where idpost = @id

END

close temp2

DEALLOCATE temp2

--объединение таблиц

declare @postname char(30)

declare temp3 scroll cursor for (select idpost, postname from sppost1)

open temp3

FETCH NEXT FROM temp3 into @id, @postname

set @i = ISNULL((select idpost from sppost where sppost.postname=@postname), 0)

if @i>0

begin

update sppost1 set idpost = @i where idpost = @id

end

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM temp3 into @id, @postname

set @i = ISNULL((select idpost from sppost where sppost.postname=@postname), 0)

if @i>0

update sppost1 set idpost = @i where idpost = @id

else

insert into sppost(idpost, postname)VALUES(@id, @postname)

END

close temp3

DEALLOCATE temp3

insert into spteachers(idpost, FIO) select idpost, FIO from spteachers1

select * from sppost

select * from sppost1

select * from spteachers

select * from spteachers1

Дата добавления: 2014-01-04