Used and Abused - 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 2000 Forums

Transact-SQL (2000)

Used and Abused

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-09-07 : 12:16:44

I'm trying to create some testing data in a script.

At the moment I create 10 people each with an ID that is generated by the system and store those ID's in a temporary table. I also have another table of contacts which indicate that contact has been made with a person. Again these have a system generated ID and are stored in a temporary table. What I want to do is tie these together so that each person has one and only one different contact.

e.g. I have a table called #TEMPID:-

ID
1
5
7
3
9
...

and a table called #TEMPCONTACTID

ContactID
23
45
67
43
21
...

What I want is a statement that will create something like

ContactID ID
23 9
45 3
67 1
43 5
21 7
...

Note that one of the columns should ideally be randomly ordered as otherwise these could both be consecutive series which is not really ideal.

Is it possible to do this? I presume that I need some extra data in at least one of my temporary tables but what? Do I need a cross join?

Many thanks

steve

Steve no function beer well without

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-07 : 12:27:35

what you need is a relationship between your two tables, of which you've shown none. How do you propose to relate TempID to TempContactID ?

- Jeff

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-09-07 : 12:43:09

I'd like them to be joined randomly which is why I was wondering about a Cross Join. I can add anything I want to these temporary tables as long as I get the final result

thanks

steve

Steve no function beer well without

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-07 : 12:44:38

quote:
Originally posted by elwoos

Steve no function beer well without

Start drinking....

OK, ahem...huh?


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE #TEMPID([ID] int)
GO

INSERT INTO #TEMPID([ID])
SELECT 1 UNION ALL
SELECT 5 UNION ALL
SELECT 7 UNION ALL
SELECT 3 UNION ALL
SELECT 9
GO

CREATE TABLE #TEMPCONTACTID (ContactID int)
GO
INSERT INTO #TEMPCONTACTID(ContactID) 
SELECT 23 UNION ALL
SELECT 45 UNION ALL
SELECT 67 UNION ALL
SELECT 43 UNION ALL
SELECT 21
GO

CREATE TABLE myTable99([ID] int, ContactID int)

DECLARE @ID int, @ContactID int

DECLARE myCursor99 CURSOR FOR SELECT [ID]      FROM  #TEMPID ORDER BY NEWID()
DECLARE myCursor00 CURSOR FOR SELECT ContactID FROM  #TEMPCONTACTID

OPEN myCursor99
OPEN myCursor00

FETCH NEXT FROM myCursor99 INTO @ID 
FETCH NEXT FROM myCursor00 INTO @ContactID

WHILE @@FETCH_STATUS = 0
  BEGIN
	INSERT INTO myTable99([ID], [ContactID])
	SELECT @ID, @ContactID

	FETCH NEXT FROM myCursor99 INTO @ID 
	FETCH NEXT FROM myCursor00 INTO @ContactID
  END

CLOSE myCursor99
CLOSE myCursor00
DEALLOCATE myCursor99
DEALLOCATE myCursor00

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE #TEMPID
DROP TABLE #TEMPCONTACTID
DROP TABLE myTable99
GO

Brett

8-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 12:50:24

could it possibly be that this is a task that can be only done with cursors or a while, as in not set based???

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-07 : 13:10:08

could it be that this is a task that doesn't make any sense?

Steve...do you know which rows are supposed to match up with which?

If so, how?

Brett

8-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 13:54:58

acctually this kind of makes sense to me...
Employees get random tasks... it's like a game "pick a card, any card from Deck"

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-07 : 14:20:22

1. insert the first list into a temp table, with an identity:

select ID, IDENTITY(1,1) as Position
into #Temp1
from IDList

2. insert the second list into a temp table, also with an identity:

select ContactID, IDENTITY(1,1) as Position
into #Temp2
from ContactIDList

3. Now we can relate ContactID's to ID's using the Position column:

select T1.ID, T2.ContactID
from #Temp1 T1
inner join #Temp2 T2
on T1.Position = T2.Position

and do what you need from there. Add ORDER BY NEWID() to either step 1 and/or 2 to add some randomness to this if needed.

- Jeff

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-09-08 : 11:08:28

Thanks guys I'll take a look at those. In response to your question Brett, Spirit is right. It's rather like having five people in a room and five cards where they have to pick one each.

Hope this is clearer

steve

Steve no function beer well without

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-09-08 : 11:11:41

Actually I've just realised that the example I gave wasn't quite right in that the #TEMPID table and the #TEMPCONTACTID table are likely to be sorted numerically, though not neccessarily consecutive numbers. i.e. #TEMPID should have been

ID
1
3
5
7
9
10 (added to show that it's not just odd numbers)
...

steve

Steve no function beer well without

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-08 : 11:45:58

did you try/follow my idea? it will work for you.

- Jeff

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-09-09 : 03:59:49

Jeff, yes it works, thanks

steve

Steve no function beer well without

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources