Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 insert в таблицы, связанные 1:1  [new]
VladimirKr
Member

Откуда: СПб
Сообщений: 1062
Здравствуйте.
SQL 2005 Express

имеется таблица t (источник):
create table t ( 
	Id  int primary key,
	a int,
	b int)


и таблицы
create  table t1( -- главная таблица-приёмник
	Id  int identity(1,1) primary key,
	a int)
	
create table t2 table( -- подчиненная таблица-приёмник (на практике связана полем id c t1.id) 
	Id  int primary key,
	b int)


По условию задачи структуры таблиц менять нельзя.
Нужно заполнить таблицы t1 и t2 данными из таблицы t следующим образом:
для каждой записи и таблицы-источника добавить в запись в таблицу t1, записав туда поле t.a, и добавить связанную запись в таблицу t2, записав туда поле b:
Например, таблица t:
id a b
28312
28721
29311
30022


требуется чтобы в таблицах t1 b t2 было:
t1.idt1.at2.idt2.b
1112
2221
3131
4242


Вот мой вариант решения:
declare  @t table( -- таблица-источник
	Id  int primary key,
	a int,
	b int)

insert into @t(Id,a,b) -- исходные данные
select
	283,1,2
union all select
	287,2,1
union all select
	293,1,1
union all select
	300,2,2
-- 

declare  @t1 table( -- главная таблица-приёмник
	Id  int identity(1,1) primary key,
	a int)
	
declare  @t2 table( -- подчиненная таблица-приёмник (на практике связана полем id c t1.id) 
	Id  int primary key,
	b int)
--
declare  @R table(ID int primary key) -- вспомогательная таблица

insert into @t1 (a)
output inserted.ID into @R(ID)
select
	a
from @t
order by Id -- NB!

insert into @t2(Id,b)
select
	R.Id,
	t.b
from (
	select 
		R.Id, 
		row_number() over(order by R.Id asc) N
	from @R R) R
inner join (
	select
		b, row_number() over(order by id) N
	from @t t) t on t.N=R.N
	
-- проверка:
select
	t1.id,
	t1.a,
	t2.id,
	t2.b
from @t1 t1
full outer join @t2 t2 on t1.id=t2.id


Этот вариант основан на предположении, что при insert в таблицу с identity совершаемым следующим образом:
insert into t(...)
select
...
from ...
order by
приращение identity будет идти согласно порядку, заданному order by в источнике строк.
Где-то я это читал, но, хоть убей не помню, для какой это версии сервера и насколько это вообще верно.
Все вроде бы работает. (Пока все идет нормально - сказал мужик пролетая мимо 6-го этажа :))
Вопрос:
Подтвердите плз или опровергните мое предположение. И если оно неверно, то каким образом решать задачу, приведенную выше?.
Спасибо.
24 май 12, 10:18    [12605978]     Ответить | Цитировать Сообщить модератору
 Re: insert в таблицы, связанные 1:1  [new]
RubinDm
Member

Откуда:
Сообщений: 461
VladimirKr, по идее, Вам точно не исмортит дело такая запись:
insert into ...
select top 100 percent ...
from ...
order by ...
Но я могу ошибаться в части понимания, способно ли оно помочь.
24 май 12, 10:25    [12606036]     Ответить | Цитировать Сообщить модератору
 Re: insert в таблицы, связанные 1:1  [new]
step_ks
Member

Откуда:
Сообщений: 936
взять t.id как альтернативный ключ что не позволяет? или вообще, перенести его в t1.id, t2.id.
24 май 12, 10:53    [12606251]     Ответить | Цитировать Сообщить модератору
 Re: insert в таблицы, связанные 1:1  [new]
VladimirKr
Member

Откуда: СПб
Сообщений: 1062
step_ks,
Ну, дык, отметил же выше, что структуры таблиц менять нельзя
24 май 12, 11:01    [12606323]     Ответить | Цитировать Сообщить модератору
 Re: insert в таблицы, связанные 1:1  [new]
step_ks
Member

Откуда:
Сообщений: 936
set identity_insert тоже нельзя?
24 май 12, 11:03    [12606338]     Ответить | Цитировать Сообщить модератору
 Re: insert в таблицы, связанные 1:1  [new]
VladimirKr
Member

Откуда: СПб
Сообщений: 1062
step_ks
set identity_insert тоже нельзя?

Да! Это не разовая операция, по условию задачи в таблицах t1 и t2 уже могут быть данные, данная операция может производиться неоднократно, кроме этого, таблицы могут наполняться данными и другими способами.
24 май 12, 11:08    [12606372]     Ответить | Цитировать Сообщить модератору
 Re: insert в таблицы, связанные 1:1  [new]
aleks2
Guest
declare  @t table( -- таблица-источник
	Id  int primary key,
	a int,
	b int)

insert into @t(Id,a,b) -- исходные данные
select
	283,1,2
union all select
	287,2,1
union all select
	293,1,1
union all select
	300,2,2
-- 

declare  @t1 table( -- главная таблица-приёмник
	Id  int identity(1,1) primary key,
	a int)
	
declare  @t2 table( -- подчиненная таблица-приёмник (на практике связана полем id c t1.id) 
	Id  int primary key,
	b int)
--
declare  @R table(ID int primary key) -- вспомогательная таблица

merge @t1 t1
using @t t
on (t1.id is null)
when not matched then
 insert (a) values(t.a )
output INSERTED.ID, t.b into @t2(id, b)
;

select * from @t2  
select * from @t1
24 май 12, 11:16    [12606438]     Ответить | Цитировать Сообщить модератору
 Re: insert в таблицы, связанные 1:1  [new]
VladimirKr
Member

Откуда: СПб
Сообщений: 1062
aleks2,
Sql srv 2005, (я отмечал выше) - merge не прокатит
24 май 12, 15:41    [12609081]     Ответить | Цитировать Сообщить модератору
 Re: insert в таблицы, связанные 1:1  [new]
ambarka_max
Member

Откуда: Россия
Сообщений: 517
Возможно facepalm.jpg, но вроде работает :)
/*
drop table t
drop table t1
drop table t2

create table t( -- таблица-источник
	Id  int primary key,
	a int,
	b int)

create table t1 ( -- главная таблица-приёмник
	Id  int identity(1,1) primary key,
	a int)
	
create table t2( -- подчиненная таблица-приёмник (на практике связана полем id c t1.id) 
	Id  int primary key,
	b int,
	CONSTRAINT [FK_t2_t1] FOREIGN KEY 
	(
		[Id]
	) REFERENCES [t1] (
		[Id]
	))

insert into t(Id,a,b) -- исходные данные
select
	283,1,2
union all select
	287,2,1
union all select
	293,1,1
union all select
	300,2,2
-- 
*/
set nocount on 

delete from t2
delete from t1

declare @r table (Id int identity(1,1) primary key,
	a int,
	b int)
declare @iden int

begin tran

select top 1 @iden=null from t with(tablockx)
select top 1 @iden=null from t1 with(tablockx)

insert t1 select 0
set @iden = SCOPE_IDENTITY()
delete from t1 where Id = @iden

insert @r (a, b)
select a, b 
from t 
order by Id
option (maxdop 1)

insert t1 (a) 
select r.a
from @r r
order by r.Id
option (maxdop 1)

insert t2 (Id, b) 
select t1.Id, r.b
from @r r
join t1 on t1.Id = r.Id + @iden
order by r.Id
option (maxdop 1)

commit tran

-- проверка:
select
	t1.id,
	t1.a,
	t2.id,
	t2.b
from t1 t1
full outer join t2 t2 on t1.id=t2.id
24 май 12, 16:54    [12609732]     Ответить | Цитировать Сообщить модератору
 Re: insert в таблицы, связанные 1:1  [new]
VladimirKr
Member

Откуда: СПб
Сообщений: 1062
ambarka_max,

Спасибо. О чем-то таком думал...
Вариант "предвосхищения" будущих значений identity.
Правда, не нравится следующее: delete для определения первого значения и dop 1.

Вопрос все еще актуален: верно ли что приращение identity идет в порядке, определенном select... order by строк для insert?
24 май 12, 17:31    [12609947]     Ответить | Цитировать Сообщить модератору
 Re: insert в таблицы, связанные 1:1  [new]
step_ks
Member

Откуда:
Сообщений: 936
VladimirKr
Вопрос все еще актуален: верно ли что приращение identity идет в порядке, определенном select... order by строк для insert?

для 2000 официально обещали, что верно. С тех пор не было обратной информации.
24 май 12, 18:13    [12610212]     Ответить | Цитировать Сообщить модератору
 Re: insert в таблицы, связанные 1:1  [new]
VladimirKr
Member

Откуда: СПб
Сообщений: 1062
step_ks
VladimirKr
Вопрос все еще актуален: верно ли что приращение identity идет в порядке, определенном select... order by строк для insert?

для 2000 официально обещали, что верно. С тех пор не было обратной информации.


Спасибо. Не поделитесь ли ссылкой?
24 май 12, 19:07    [12610393]     Ответить | Цитировать Сообщить модератору
 Re: insert в таблицы, связанные 1:1  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
> Спасибо. Не поделитесь ли ссылкой?

http://support.microsoft.com/kb/273586/en-us/
24 май 12, 19:16    [12610442]     Ответить | Цитировать Сообщить модератору
 Re: insert в таблицы, связанные 1:1  [new]
VladimirKr
Member

Откуда: СПб
Сообщений: 1062
daw
http://support.microsoft.com/kb/273586/en-us/


Спасибо.
24 май 12, 19:19    [12610457]     Ответить | Цитировать Сообщить модератору
 Re: insert в таблицы, связанные 1:1  [new]
VladimirKr
Member

Откуда: СПб
Сообщений: 1062
Мда. Жаль что для 2005 это только неофициальное честное слово разработчиков
24 май 12, 19:40    [12610530]     Ответить | Цитировать Сообщить модератору
 Re: insert в таблицы, связанные 1:1  [new]
aleks2
Guest
VladimirKr
Мда. Жаль что для 2005 это только неофициальное честное слово разработчиков


От таких заяв не отказываются
http://support.microsoft.com/kb/273586/en-us?fr=1
25 май 12, 06:10    [12611817]     Ответить | Цитировать Сообщить модератору
 Re: insert в таблицы, связанные 1:1  [new]
VladimirKr
Member

Откуда: СПб
Сообщений: 1062
[quot aleks2]
VladimirKr
От таких заяв не отказываются
http://support.microsoft.com/kb/273586/en-us?fr=1

Это для 7-2000. Там так и сказано...
25 май 12, 08:44    [12612027]     Ответить | Цитировать Сообщить модератору
 Re: insert в таблицы, связанные 1:1  [new]
step_ks
Member

Откуда:
Сообщений: 936
declare  @t table( -- таблица-источник
	id  int primary key,
	a int,
	b int)

insert into @t(Id,a,b) -- исходные данные
select
	283,1,2
union all select
	287,2,1
union all select
	293,1,1
union all select
	300,2,2
-- 

declare  @t1 table( -- главная таблица-приёмник
	id  int identity(1,1) primary key,
	a int)
	
declare  @t2 table( -- подчиненная таблица-приёмник (на практике связана полем id c t1.id) 
	Id  int primary key,
	b int)
--

declare  @R table(id int primary key, id_t int) -- вспомогательная таблица


insert into @t1 (a)
output inserted.id, inserted.a into @R(id,id_t)
select id
from @t

update t1
    set a=t.a
    output inserted.id, t.b into @t2(id,b)
    from @t1 t1
         join @r r on r.id=t1.id
         join @t t on r.id_t=t.id


select * from @t
select * from @t1
select * from @t2

25 май 12, 09:00    [12612094]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить