Just stumped myself on a simple query.

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)

Just stumped myself on a simple query.

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-04-21 : 11:26:23

If I have 3 tables and need to link when no linking column is present.

Note, this is not my design, so please bare with me!


Declare @Source table (ID int identity(1,1) ,val varchar(20))
Declare @Destination table (ID2 int identity(22,1),val varchar(20))
Declare @Link table (ID int, id2 int)

insert @Source(Val)
select 'test1' Union ALL
select 'test1' Union ALL
select 'tst2' Union ALL
select 'test3'

insert @Destination(Val)
select val from
@Source


--INSERT @LINK
--DESIRED RESULTS FOR @LINK TABLE
/* ID, ID2
1,22
2,23
3,24
4,25
*/

I was thinking of using a row_number and order by the Identity then just reproduce, the issue is it's not a ideal solution.

Any suggestions, and keep in mind recreate the tables is not a option.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-21 : 11:41:18

With the given informationen I have this idea:
select
s.ID,
d.ID2
from @Source s
join @Destination d
on s.ID = d.ID2 - 21

But the solution with row_number() should work.

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

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-04-21 : 11:46:18

I just used the identity(21,1) to illustrate they were not linking, it's really not a actual representation. I will probably need to implement some table locking hints, and use the row_number method, but I appriciate your help!

Here's my thoughts,

1. Lock both source and Destination Table and insert ordering by identity
2. Output results to a temp table
3, Insert into link table by joining on the row number.

It's a lot messier than I would like, but I guess it is what it is.

Thanks!

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-21 : 11:52:36

Maybe I misunderstood, but this should work:
select dt1.ID, dt2.ID2 from
(select row_number() over (order by ID) as rownum, ID from @Source)dt1
join
(select row_number() over (order by ID2) as rownum, ID2 from @Destination)dt2
on dt1.rownum = dt2.rownum

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

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-04-21 : 12:43:33

That works in theory, but my worry is if other inserts happen on the live table simultaneously and somehow causes the destination to have more values than the source causing it no longer to link properly. Regardless, I got it to work, and I can chalk this up as done.

Thanks

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-21 : 14:26:09

You can change the transaction isolation mode during the select.

N 56°04'39.26"
E 12°55'05.63"

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-04-21 : 20:55:23

Again Peso you have taught me something new. That would've been much cleaner than the method I used, and I'm sure it will come in handy in the future.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources