instead of a temp table ... - 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

SQL Server Development (2000)

instead of a temp table ...

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2003-06-27 : 16:36:51

What's the best way to do this?

Sample Mytable structure:
ID acct icn userid password reissuedto?
1 123456 1001 user1 password 4
2 123457 1001 user2 password <null>
3 123458 1002 user3 password <null>
4 123459 1001 <null> <null> <null>

Here's a pseudocode of what I want to do:
Create a temp table
Given the icn, userid, and password get the acct number and reissuedto values and insert into temp table
if reissuedto is not null, get the new acct number with ID=reissuedto
update the acct field in the temp table
select the account number from the temp table

Is there a more efficient way of doing this other than using a temp table?

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-27 : 16:48:08

How about the following:


CREATE Table myTablex (myID int, acct int,  icn int,  userid varchar(5),  pwd varchar(8), reissuedto int)
GO

INSERT INTO myTablex (myID, acct,  icn,  userid,  pwd, reissuedto) 
SELECT 1, 123456, 1001, 'user1', 'password', 4 UNION ALL 
SELECT 2, 123457, 1001, 'user2', 'password', NULL UNION ALL
SELECT 3, 123458, 1002, 'user3', 'password', NULL UNION ALL
SELECT 4, 123459, 1001, NULL, NULL, NULL
GO

    SELECT l.acct 
      FROM myTablex l 
INNER JOIN myTablex r
        ON l.myID = r.reissuedto

GO

DROP TABLE myTablex
GO

And since Nulls are eliminated from the predicate you don't have to worry about them.

Brett

8-)

PeterG
Posting Yak Master

156 Posts

Posted - 2003-06-27 : 16:59:10

Thanks Brett.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources