Replacing temp table with table variable

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

General SQL Server Forums

New to SQL Server Programming

Replacing temp table with table variable

Author

Topic

mrkensr
Starting Member

9 Posts

Posted - 2008-09-17 : 20:28:18

Having some trouble with this:
OLD:
Create TABLE #ACCTS
(TEMP_ACCT varchar(14),
Tot_Count int,
TEMP_Id varchar(12),
Shares_Count int)

Insert into #ACCTS(TEMP_ACCT,Tot_Count,Shares_Count)
(Select ACCOUNTS.Account_Num,(Select count(*) from x),
(Select count(*) from shares)
From ACCOUNTS

Update #ACCTS Set TEMP_ID='MULTI' where Tot_Count>1 AND Shares_Count=0

Update #ACCTS Set TEMP_ID=(Select ID from Assets as A
Inner join Holding H on H.ID=A.ID
AND H.Account=#ACCTS.TEMP_ACCT)
Where Tot_Count=1

.......
Select Super_Id=#ACCTS.TEMP_ID

THE NEW??------------------------

Declare @ACCTS TABLE
(TEMP_ACCT varchar(14),
Tot_Count int,
TEMP_Id varchar(12),
Shares_Count int)

Insert into @ACCTS(TEMP_ACCT,Tot_Count,Shares_Count)
(Select ACCOUNTS.Account_Num,(Select count(*) from x),
(Select count(*) from shares)
From ACCOUNTS

Update @ACCTS Set TEMP_ID='MULTI' where Tot_Count>1 AND Shares_Count=0

Update @ACCTS Set TEMP_ID=(Select ID from Assets as A
Inner join Holding H on H.ID=A.ID
AND H.Account=#ACCTS.TEMP_ACCT)
Where Tot_Count=1

.......
Select Super_Id=#ACCTS.TEMP_ID

I Cant find any info on how to make this work
Help please.........

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 00:01:22

change like below

Update a 
Set a.TEMP_ID=t.ID
FROM @ACCTS a
JOIN (Select ID,H.Account from Assets as A
Inner join Holding H on H.ID=A.ID
)t
ON t.Account=a.TEMP_ACCT
Where a.Tot_Count=1

.......
Select Super_Id=a.TEMP_ID
FROM @ACCTS a
...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources