merge two tables together - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

merge two tables together

Author

Topic

ttran
Starting Member

23 Posts

Posted - 2007-05-21 : 20:33:16

Could someone help me with this case?
I have two tables, tbl1 and tbl2. I want to insert into tbl2 all the rows from tbl1 start with last seq# of tbl2 + 1. Both tables have primary key is pid+sq

tbl1
pid tst pt sc sq
1 C 1 30 1
1 C 2 20 2
1 C 1 25 3
2 B 4 15 1
2 B 5 35 2
3 C 1 25 1
3 C 4 20 2

tbl2
pid tst pt sc sq
1 A 5 50 1
1 A 6 30 2
2 A 5 10 1
2 A 4 20 2

The result would be:

tbl2
pid tst pt sc sq
1 A 5 50 1
1 A 6 30 2
1 C 1 30 3
1 C 2 20 4
1 C 1 25 5
2 A 5 10 1
2 A 4 20 2
2 B 4 15 3
2 B 5 35 4
3 C 1 25 1
3 C 4 20 2

What I did is to create a view to find the max(sq) on tbl2 for each pid, then insert into tbl2 from tbl1 based on the max(sq) of the view, plus 1. I got the error: "Violation of Primary Key contraint. Cannot insert duplicate key in object dbo.tbl2."
Seems like the insert statement uses the same value of max(sq) instead of increase 1 after each inserting

create view maxsq as
select pid,max(sq) as sq
from tbl2
group by pid

insert into tbl2(pid,tst,pt,sc,sq)
select a.pid,a.tst,a.pt,a.sc,
case when (b.sq is null) then 1
else (b.sq + 1) end
from tbl1 a left outer join maxsq b on a.pid = b.pid

I really appreciate if someone can help me.
Thanks in advance.
--ttran

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-21 : 21:43:43

[code]
declare @tbl1 table
(
pid int,
tst char(1),
pt int,
sc int,
sq int
)
insert into @tbl1
select 1, 'C', 1, 30, 1 union all
select 1, 'C', 2, 20, 2 union all
select 1, 'C', 1, 25, 3 union all
select 2, 'B', 4, 15, 1 union all
select 2, 'B', 5, 35, 2 union all
select 3, 'C', 1, 25, 1 union all
select 3, 'C', 4, 20, 2

declare @tbl2 table
(
pid int,
tst char(1),
pt int,
sc int,
sq int
)
insert into @tbl2
select 1, 'A', 5, 50, 1 union all
select 1, 'A', 6, 30, 2 union all
select 2, 'A', 5, 10, 1 union all
select 2, 'A', 4, 20, 2

insert into @tbl2 (pid, tst, pt, sc, sq)
select t1.pid, t1.tst, t1.pt, t1.sc,
new_sq = isnull(l.last_sq, 0) + (row_number() over (partition by t1.pid order by t1.sq))
from @tbl1 t1 left join
(
select pid, last_sq = max(sq)
from @tbl2
group by pid
) l
on t1.pid = l.pid

select *
from @tbl2
order by pid, sq

/* result
pid tst pt sc sq
----------- ---- ----------- ----------- -----------
1 A 5 50 1
1 A 6 30 2
1 C 1 30 3
1 C 2 20 4
1 C 1 25 5
2 A 5 10 1
2 A 4 20 2
2 B 4 15 3
2 B 5 35 4
3 C 1 25 1
3 C 4 20 2

(11 row(s) affected)
*/
[/code]

ttran
Starting Member

23 Posts

Posted - 2007-05-22 : 12:00:43

Thank you so much.
You're awesome.
You don't know how much it helps me. I know this request will be on my desk again and again.
--ttran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources