insert values from 3 temp tables into 1 table

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 2008 Forums

Transact-SQL (2008)

insert values from 3 temp tables into 1 table

Author

Topic

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-12-09 : 07:43:01

Hi,

I have 3 temp tables displaying the following results

1.

FG_PriceListID TW_FG_Line_ID
31617-----------10062127
31617-----------10062128
31617-----------10062129
31617-----------10062130
31617-----------10062131
31617-----------10062132
31617-----------10062133
31617-----------10062134

2.

FG_PriceListID T5_FG_Line_ID
31617-----------10062135
31617-----------10062136
31617-----------10062137
31617-----------10062138
31617-----------10062139
31617-----------10062140
31617-----------10062141
31617-----------10062142

3.

FG_PriceListID T9_FG_Line_ID
31617-----------10062143
31617-----------10062144
31617-----------10062145
31617-----------10062146
31617-----------10062147
31617-----------10062148
31617-----------10062149
31617-----------10062150

I would like to insert them into a table along side each other so the resulting table would look like :

FG_PriceListID TW_FG_Line_ID T5_FG_Line_ID T9_FG_Line_ID
31617-----------10062127-----------10062135-----------10062143
31617-----------10062128-----------10062136-----------10062144
31617-----------10062129-----------10062137-----------10062145
31617-----------10062130-----------10062138-----------10062146
31617-----------10062131-----------10062139-----------10062147
31617-----------10062132-----------10062140-----------10062148
31617-----------10062133-----------10062141-----------10062149
31617-----------10062134-----------10062142-----------10062150

I was hoping to do some sort of outer join but the result set is not coming back how I need it to be.

Can anyone offer any advice on how to achieve this. I realize it seems a bit awkward but the data i am working with is not structured in the best way.

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-12-09 : 07:49:32

select t1.FG_PriceListID, t1.TW_FG_Line_ID, t2.T5_FG_Line_ID, t3.T9_FG_Line_ID
from table_1 as t1
left join table_2 as t2 on t1.FG_PriceListID = t2.FG_PriceListID
left join table_3 as t3 on t1.FG_PriceListID = t3.FG_PriceListID

No, you're never too old to Yak'n'Roll if you're too young to die.

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-12-09 : 07:53:38

Thank you webfred.

This is creating the results alright but they are getting multiplied, so the result set is 512 rows (8*8*8)

I would just like the 8 results with the 4 columns of data - FG_PriceListID TW_FG_Line_ID T5_FG_Line_ID T9_FG_Line_ID

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-12-09 : 08:16:02

select DISTINCT ...

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-12-09 : 08:19:03

Ah - I understand now...
Sorry.
Wait a moment...

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-12-09 : 08:30:20

[code]declare @sample1 table(FG_PriceListID int, TW_FG_Line_ID int)

insert @sample1
select 31617,10062127 union all
select 31617,10062128 union all
select 31617,10062129 union all
select 31617,10062130 union all
select 31617,10062131 union all
select 31617,10062132 union all
select 31617,10062133 union all
select 31617,10062134

declare @sample2 table(FG_PriceListID int, T5_FG_Line_ID int)
insert @sample2
select 31617,10062135 union all
select 31617,10062136 union all
select 31617,10062137 union all
select 31617,10062138 union all
select 31617,10062139 union all
select 31617,10062140 union all
select 31617,10062141 union all
select 31617,10062142

declare @sample3 table(FG_PriceListID int, T9_FG_Line_ID int)

insert @sample3
select 31617,10062143 union all
select 31617,10062144 union all
select 31617,10062145 union all
select 31617,10062146 union all
select 31617,10062147 union all
select 31617,10062148 union all
select 31617,10062149 union all
select 31617,10062150

select t1.FG_PriceListID, t1.TW_FG_Line_ID, t2.T5_FG_Line_ID, t3.T9_FG_Line_ID
from
(select row_number() over (partition by FG_PriceListID order by TW_FG_Line_ID) as rnum,* from @sample1)t1

left join
(select row_number() over (partition by FG_PriceListID order by T5_FG_Line_ID) as rnum,* from @sample2)t2
on t1.FG_PriceListID = t2.FG_PriceListID and t1.rnum = t2.rnum

left join
(select row_number() over (partition by FG_PriceListID order by T9_FG_Line_ID) as rnum,* from @sample3)t3
on t1.FG_PriceListID = t3.FG_PriceListID and t1.rnum = t3.rnum

[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-12-09 : 09:03:41

Thanks very much webfred, works perfect