CODEWITHSUNDEEP
sqlsql-server
armen
armen

Reputation: 1283

SQL : insert data from one table into two tables related to each other

i have a table tSource which is a SELECT result of a cartesian product, so there is no unique ID in the set. In short lets say the table looks like the following:

tSource
-------
f1 | f2
-------
 H |  a
 I |  b
 J |  c
 K |  d
 K |  d

i need to "split" the data of tSource into tbl1 and tbl2 which are related to each other:

tbl1             tbl2
-------          -----------------
ID | f1          ID | tbl1_ID | f2
-------          -----------------
11 |  H          51 |      11 |  a
12 |  I          52 |      12 |  b
13 |  J          53 |      13 |  c
14 |  K          54 |      14 |  d
15 |  K          55 |      15 |  d

ID Columns in both destination tables are INT IDENTITY

any help would be appreciated, thanx in advance

Upvotes: 0

Views: 634

Answers (3)

cproinger
cproinger

Reputation: 2288

this is not intirely correct SQL (because i don't know the types) but i think you will get the idea

create table tbl1(ID primary key identity, f1, f2)

insert into tbl1(f1, f2) select f1, f2 from tSource

create table tbl2(ID primary key identity, tbl1_ID not null, f2)

insert into tbl2(tbl1_ID, f2) select ID, f2 from tbl1

alter table tbl1 drop column f2

alter table tbl2 add constraint myForeignKey foreignkey(tabl1_ID) references tbl1(ID)

Upvotes: 0

Serge
Serge

Reputation: 6692

Do the two insertions operations altogether in a MERGE + OUTPUT statement.

merge @table2 as t2
using (
    select *
    from @table
) as src
on (1 = 2)
when not matched then
    insert (f1)
    values (src.f1)
output inserted.ID, src.f2 into @table3 (f1ID, f2)
;

complete example:

declare @table table (
    f1 char(1)
    , f2 char(1)
)

insert @table
values
('H', 'a')
, ('I', 'b')
, ('J', 'c')
, ('K', 'd')


declare @table2 table (
    ID int not null identity
    , f1 char(1)
)

declare @table3 table (
    ID int not null identity
    , f1ID int not null
    , f2 char(1)
)

merge @table2 as t2
using (
    select *
    from @table
) as src
on (1 = 2)
when not matched then
    insert (f1)
    values (src.f1)
output inserted.ID, src.f2 into @table3 (f1ID, f2)
;

select *
from @table2

select *
from @table3

Upvotes: 2

Amit Singh
Amit Singh

Reputation: 8109

Part1-:

insert into tbl1(f1)  select f1 from tSource;

Part2-:

Insert into Tabl2 (tbl1_id,f2)
(
Select id,f2 from (Select Row_Number() Over(Partition by id order by id) as row,t1.f2,t2.id from t1 ,t2) a 
where row=(Select r from
( Select Row_Number() over(Order by id)as r,id  from t2) b where b.id=a.id)
)

here What Select Of Part 2 Return ....SQL Fiddle Demo

Upvotes: 1

Related Questions