| Author |
 Topic  |
|
|
Zath
Constraint Violating Yak Guru
USA
255 Posts |
 Posted - 11/19/2012 : 19:02:51
|
This may be continued from my last post but that was solved and ran into another problem.
I am getting too many rows back and I understand why but can't seem to be able to solve it.
DECLARE @tmpTbl1 TABLE
(
id BIGINT
);
DECLARE @tmpTbl2 TABLE
(
id BIGINT
);
INSERT INTO @tmpTbl1(id)VALUES(1)
INSERT INTO @tmpTbl1(id)VALUES(2)
INSERT INTO @tmpTbl2(id)VALUES(3)
INSERT INTO @tmpTbl2(id)VALUES(4)
SELECT
tbl1.id as tbl1
, tbl2.id as tbl2
FROM @tmpTbl1 as tbl1, @tmpTbl2 as tbl2
Of course the result is:
tbl1 tbl2
1 3
2 3
1 4
2 4
What I need is this because the first row in tbl1 will need to match the first row in tbl2 and so on:
tbl1 tbl2
1 3
2 4
The code above may be copied and ran for your convience.
thanks for any and all input!
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/19/2012 : 20:01:23
|
| It is possible with ROW_NUMBER() OVER(PARTITION BY....) function but can you tell us why you need cross product or there is no matching field between two tables. |
 |
|
|
Zath
Constraint Violating Yak Guru
USA
255 Posts |
Posted - 11/20/2012 : 00:06:46
|
It is actually an insert but in order to get it working I was doing a select....
INSERT INTO MyTable
(FirstFK]
,[SecondFK])
SELECT
,fstLst.id
,sndLst.id
FROM @firstLIST as fstLst, @secondLIST as sndLst
Every row in both lists match. So, row 1 = row 1, and so forth....
Been stuck on this....
|
Edited by - Zath on 11/20/2012 00:08:37 |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5467 Posts |
Posted - 11/20/2012 : 01:14:51
|
You mean this - which assumes that your rows are in order of IDs. So if your IDs can be out of numeric sequence then this won't work. ie: (1,3,2)
select t1.id as t1, t2.id as t2
from (
select id, row_number() over (order by id) as rn
from @tmpTbl1
) t1
join (
select id, row_number() over (order by id) as rn
from @tmpTbl2
) t2 on t2.rn = t1.rn
OUTPUT:
t1 t2
-------------------- --------------------
1 3
2 4
Be One with the Optimizer
TG |
Edited by - TG on 11/20/2012 01:17:27 |
|
|
|
Zath
Constraint Violating Yak Guru
USA
255 Posts |
Posted - 11/20/2012 : 11:58:59
|
After doing some testing today, there is still a problem.
Like I said, I was trying to get the code working using a select but it's actually for an insert.
I was expecting it to bring back one value at a time while inserting multiple rows, so each row will be inserted with the next one in the list using your code.
But it's bring back too many values at once.
This is going to be a stubborn one.
|
Edited by - Zath on 11/20/2012 12:57:48 |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/20/2012 : 14:46:13
|
You mean this
select t1.id as t1, t2.id as t2
from (
select id, row_number() over (order by id) as rn
from @tmpTbl1
) t1 and t1.rn = 1
join (
select id, row_number() over (order by id) as rn
from @tmpTbl2
) t2 on t2.rn = t1.rn |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5467 Posts |
Posted - 11/20/2012 : 15:30:27
|
Well, this would just return the "first" row:
quote:
Originally posted by sodeep
You mean this
select t1.id as t1, t2.id as t2
from (
select id, row_number() over (order by id) as rn
from @tmpTbl1
) t1 and t1.rn = 1
join (
select id, row_number() over (order by id) as rn
from @tmpTbl2
) t2 on t2.rn = t1.rn and t1.rn = 1
But my question is why don't you want to insert all rows at once?
Perhaps an overview of what you're tying to accomplish would help us help you. Looks like a questionable design with what you've posted so far.
Be One with the Optimizer
TG |
|
|
| |
Topic |
|
Select from two lists
