каскадное копирование TSQL

← →
pleasure © (2010-02-18 13:28) [0]

существует три взаимосвязанных таблицы a->b->c ("c" подчинена "b", а "b" подчинена "а") меж ними связь один ко многим. каскадное удаление происходит на ура. а вот каким образом реализовать каскадное копирование ?
к примеру в таблице "а" копируем одну из строчек. следом копировать все свзяанные строки из "b" в неё же но с новым a.id, далее свзяанные строки из "с" ....
Проще говоря делается справочник прейскуранта услуг. а - прейскурант, b - услуги, с - составляющие услуг.
копирование предназначено для того, чтобы не вносить новые услуги и их составляющие с нуля.
к тому же у составляющих услуг должна быть ссылка адекватная новому Id услуги.
у кого какие соображения по этому поводу?

← →
Sergey13 © (2010-02-18 13:34) [1]

Ну так копировать во вложенных циклах с подменой старых связей на новые.

← →
Ega23 © (2010-02-18 13:37) [2]

Хранимка.

← →
pleasure © (2010-02-18 13:41) [3]

возможно решение через тригер на данные таблицы inserted

← →
pleasure © (2010-02-18 13:45) [4]

у меня выглядит
INSERT into Preiskur ([name], [data], deistv, idDogov)
values (@name,CAST(@data as smalldatetime),"1",@idDogov)
select @idd =@@IDENTITY
-- копирование в Справочник услуг услуг со старого прейскуранта
insert into SUslugi ([name], idPreisk, idSOtd, kod, cena, tarifOMS,
vnerealiz, TimeUsl, PrRacx, KocPacx, KocPacxPr, ItogPacx, Rentab,
RentabPr, ZPL, idparent, Amortiz, oldID)
select [name],@idd, idSOtd, kod, cena, tarifOMS, vnerealiz, TimeUsl,
PrRacx, KocPacx, KocPacxPr, ItogPacx, Rentab, RentabPr,
ZPL, "0", Amortiz, id
from SUslugi WHERE SUslugi.idPreisk=@id

но это начало копируются старые услуги в новый прейскурант, а составляющие нет ;(, да и не должны. а вот отследить свзяанн.... короче не осиляю я такой длинной связки пока через запросы ((((((

← →
pleasure © (2010-02-18 13:55) [5]

точно, надо на тригеры вставки повесить обработчики, пущай они и думают, а не мне голову ломать ;))))

← →
Sergey13 © (2010-02-18 13:59) [6]

> [5] pleasure © (18.02.10 13:55)
> пущай они и думают, а не мне голову ломать ;))))

Хороший подход. Плодотворный.

← →
12 © (2010-02-18 14:01) [7]

> Хранимка.

+1

> точно, надо на тригеры вставки повесить обработчики,

а потом еще и еще
а потом забыли про половину
а потом удивляемся, а чего это у нас идеальный с виду код так тормозит

← →
Sergey13 © (2010-02-18 14:06) [8]

Тригер тут вообще не к месту упомянут, ИМХО.

← →
pleasure © (2010-02-18 14:07) [9]

Sergey13, а ваше предложение?

← →
pleasure © (2010-02-18 14:13) [10]

не тригры точно здесь не помогут при а и b ещё прокатит а вот дальше начинается полнейший бардак. поторопился я (((

← →
Sergey13 © (2010-02-18 14:18) [11]

> [9] pleasure © (18.02.10 14:07)

Я их написал в первом ответе.

← →
Ega23 © (2010-02-18 14:26) [12]

DDL на таблицы давай.

← →
pleasure © (2010-02-18 14:31) [13]

CREATE TABLE [dbo].[SPreiskur] (
[id] int IDENTITY(1, 1) NOT NULL,
[name] varchar(100) COLLATE Cyrillic_General_CI_AS NULL,
[data] smalldatetime DEFAULT getdate() NOT NULL,
[deistv] smallint DEFAULT 0 NOT NULL,
[idDogov] int NULL,
PRIMARY KEY CLUSTERED ([id]),
CONSTRAINT [SPreiskur_fk] FOREIGN KEY ([idDogov])
REFERENCES [dbo].[SDogovor] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[SUslugi] (
[id] int IDENTITY(1, 1) NOT NULL,
[name] varchar(250) COLLATE Cyrillic_General_CI_AS DEFAULT "-" NULL,
[kod] char(15) COLLATE Cyrillic_General_CI_AS DEFAULT "99999" NULL,
[cena] money DEFAULT 0 NOT NULL,
[tarifOMS] money DEFAULT 0 NOT NULL,
[vnerealiz] money DEFAULT 0 NOT NULL,
[TimeUsl] float DEFAULT 0 NOT NULL,
[PrRacx] money DEFAULT 0 NOT NULL,
[KocPacx] money DEFAULT 0 NOT NULL,
[KocPacxPr] float DEFAULT 0 NOT NULL,
[ItogPacx] money DEFAULT 0 NOT NULL,
[Rentab] money DEFAULT 0 NOT NULL,
[RentabPr] float DEFAULT 0 NOT NULL,
[ZPL] money DEFAULT 0 NOT NULL,
[Amortiz] money DEFAULT 0 NOT NULL,
[idSOtd] int NULL,
[idParent] int DEFAULT 99999 NULL,
[idPreisk] int NULL,
[USERNAME] varchar(75) COLLATE Cyrillic_General_CI_AS DEFAULT user_name() NOT NULL,
[MiagInv] money DEFAULT 0 NOT NULL,
[MInvTime] float DEFAULT 0 NOT NULL,
[prPervichn] bit DEFAULT 0 NOT NULL,
PRIMARY KEY CLUSTERED ([id]),
CONSTRAINT [SUslugi_fk] FOREIGN KEY ([idPreisk])
REFERENCES [dbo].[SPreiskur] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT [SUslugi_fk2] FOREIGN KEY ([idSOtd])
REFERENCES [dbo].[SOtdelen] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[EMedik] (
[id] int IDENTITY(1, 1) NOT NULL,
[idMedik] int NULL,
[idUslugi] int NULL,
[pacxod] float DEFAULT 0 NOT NULL,
[stoim] money DEFAULT 0 NOT NULL,
PRIMARY KEY CLUSTERED ([id]),
CONSTRAINT [EMedik_fk] FOREIGN KEY ([idUslugi])
REFERENCES [dbo].[SUslugi] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT [EMedik_fk2] FOREIGN KEY ([idMedik])
REFERENCES [dbo].[SMedik] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
)
ON [PRIMARY]
GO

← →
Ega23 © (2010-02-18 15:05) [14]

А дальше?
Что со всем этим нужно делать-то? Где бизнес-логика?

← →
pleasure © (2010-02-18 15:15) [15]

а - прейскурант, b - услуги, с - составляющие услуг.
Старый прейскурант включает услуги, а услуги в свою очередь подразделяются на составляющие (медикаменты, работа и т.д.).
Нужно завести новый прейскурант. Чтобы не заносить все услуги и составляющие услуг делаем их копию из старого прейскуранта.

через for и while понятно как, а вот через запросы в ХП несовсем понятно как это реализовать. Точнее до половины "pleasure © (18.02.10 13:45) [4]" а дальше хз

← →
Ega23 © (2010-02-18 15:25) [16]

Вечером напишу.

← →
pleasure © (2010-02-18 16:09) [17]

а мне видно пора опять книжку перечитать по TSQL, но уже внимательнее (((

← →
pleasure © (2010-02-19 09:14) [18]

> Ega23 © (18.02.10 15:25) [16]
>
> Вечером напишу.

;((

← →
12 © (2010-02-19 09:54) [19]

> ;((

там делов то - сам бы написал давно

и я бы написал тебе, просто в sql синиаксис долгий, на каждый вздох - оператор, просто в лом писать :)

← →
pleasure © (2010-02-19 10:09) [20]

каркас может не сложно накидать?

← →
Ega23 © (2010-02-19 11:34) [21]

Щас накидаю.

← →
b z (2010-02-19 11:45) [22]

Например два select into должны справиться.

← →
Ega23 © (2010-02-19 12:12) [23]

Create Table FirstTable ( FID int identity (1,1), FName varchar(32) not null, constraint PK_FirstTable primary key (FID) ); Create Table SecondTable ( SID int identity (1,1), FID int not null, SName varchar(32) not null, constraint PK_SecondTable primary key (SID) ); Create Table ThirdTable ( TID int identity (1,1), SID int not null, TName varchar(32) not null, constraint PK_TirdTable primary key (TID) ); alter table SecondTable add constraint FK_Second_Ref_First foreign key (FID) references FirstTable (FID); alter table ThirdTable add constraint FK_Third_Ref_Second foreign key (SID) references SecondTable (SID); Set NoCount ON; Insert into FirstTable (FName) Values ("First_1"); Insert into FirstTable (FName) Values ("First_2"); Insert into SecondTable (FID, SName) Values (1, "Second_1_1"); Insert into SecondTable (FID, SName) Values (1, "Second_1_2"); Insert into SecondTable (FID, SName) Values (2, "Second_2_1"); Insert into SecondTable (FID, SName) Values (2, "Second_2_2"); Insert into ThirdTable (SID, TName) Values (1, "Third_1_1_1"); Insert into ThirdTable (SID, TName) Values (1, "Third_1_1_2"); Insert into ThirdTable (SID, TName) Values (2, "Third_1_2_1"); Insert into ThirdTable (SID, TName) Values (2, "Third_1_2_2"); Insert into ThirdTable (SID, TName) Values (3, "Third_1_3_1"); Insert into ThirdTable (SID, TName) Values (3, "Third_1_3_2"); Insert into ThirdTable (SID, TName) Values (4, "Third_1_4_1"); Insert into ThirdTable (SID, TName) Values (4, "Third_1_4_2");

← →
Ega23 © (2010-02-19 12:12) [24]

if exists (select * from sysobjects where id = object_id(N"[S_MyProc]") and OBJECTPROPERTY(id, N"IsProcedure") = 1) drop procedure [S_MyProc] GO CREATE PROCEDURE S_MyProc @ActNam varchar(32)="NONE", @ID int = -1, @parID int = -1, @Name varchar(32) = "" As declare @aName varchar (32), @aID int, @x int; if (@ID = -1) Goto FIN; Set NoCount ON; Set @ActNam = Upper(LTrim(RTrim(@ActNam))); Set @aName = NULL; if @ActNam = "FIRST.ADD" begin Select @aName = FName from FirstTable where FID = @ID; if @aName is null Goto FIN; -- Нет записей c таким ID Insert into FirstTable (FName) Values (@aName); Select @aID = Scope_Identity(); Declare Cur cursor local static for Select SID, SName from SecondTable where FID = @ID; Open Cur; While (0=0) begin Fetch Next from Cur into @x, @aName; if @@FETCH_STATUS <> 0 Break; exec S_MyProc @ActNam = "SECOND.ADD", @ID = @x, @parID = @aID, @Name = @aName; end; Close Cur; Deallocate Cur; Goto FIN; end; if @ActNam = "SECOND.ADD" begin Select @aName = SName from SecondTable where SID = @ID; if @aName is null Goto FIN; -- Нет записей c таким ID Insert into SecondTable (FID, SName) Values (@parID, @aName); Select @aID = Scope_Identity(); Insert into ThirdTable (SID, TName) Select SID=@aID, TName from ThirdTable where SID = @ID; Goto FIN; end; FIN: return(0); GO

← →
Ega23 © (2010-02-19 12:13) [25]

Использование:

exec S_MyProc @actNam= "FIRST.ADD", @ID=1 Select * from FirstTable Select * from SecondTable Select * from ThirdTable

← →
pleasure © (2010-02-19 12:39) [26]

o_O. Не может быть но работает корректно )), алгоритм ещё стоит разобрать и понять. Ega23 должен буду ;). С наступающим Вас.

> алгоритм ещё стоит разобрать и понять

Обрати внимание на Scope_Identity(), в отличие от @@Identity.
Это тонкий момент, по нему посмотри в BOL.

у меня MSSQL2000

> у меня MSSQL2000

я в синтаксисе 2000 и написал всё, "примочек" из 2005 и выше не использовал (да и не нужны они тут)

Функции SCOPE_IDENTITY и @@IDENTITY возвращают последние значения идентификатора, созданные в таблицах во время текущего сеанса. Однако функция SCOPE_IDENTITY возвращает значения, вставленные только в рамках текущей области, тогда как действие функции @@IDENTITY не ограничивается никакими областями.

Угу. Т.е. у тебя таблица1 с identity и таблица2 c identity. И на первой таблице триггер висит на Insert. и в этом триггере идёт вставка чего-то во вторую.
Так вот, в этом случае Scope_Identity и @@Identity с большой вероятностью не совпадут, т.к. первая вернёт именно значение, вставленное в первую таблицу, а @@Identity - значение вставленной записи во вторую.

короче, если во время вставки, триггер вдруг сработает какой - @@IDENTITY могет быть не той, которую ожидаешь

а не секрет если, что за контора? Имхо, в Саранске мало кому надо именно _писать_

Сам отсюда %)

Просто у тебя в скрипте как раз @@Identity использовалось, глаза резануло.

← →
b z (2010-02-19 13:21) [34]

declare @i int insert into dbo.FirstTable (fname) values("123") select @i = scope_identity() insert into dbo.SecondTable select @i as fid, st.sname as sname from dbo.SecondTable as st join dbo.FirstTable as ft on ft.fid=st.fid where ft.fid=1 insert into dbo.ThirdTable select (select st2.sid from dbo.SecondTable as st2 where st2.fid=@i and st.sname=st2.sname) as sid, tt.tname as tname from dbo.ThirdTable as tt join dbo.SecondTable as st on st.sid=tt.sid join dbo.FirstTable as ft on ft.fid=st.fid where ft.fid=1

вроде так проще и понятнее. :)

> вроде так проще и понятнее. :)

Нет проверки, если в FirstTable нет записей с FID=1

Ну и потом, я специально такую мудрёную структуру наваял. Тут и пример с курсором, и проверка на отсутствие записей, и рекурсивный вызов ХП, и конструкция Insert into Select from
А дальше автор пусть как хочет так и делает.

← →
b z (2010-02-19 14:04) [38]

> Нет проверки, если в FirstTable нет записей с FID=1
Тут же будет параметр его не с потолка берут, а если запись успели удалить (долго принимали решение, на клонирование) - на откуп пользователям. ;) Можно добавить и проверку +
> А дальше автор пусть как хочет так и делает.
.

Я к курсорам/рекурсиям прибегаю когда уж совсем без них никак.

каскадное копирование TSQL Найти похожие ветки