SQL Server Performance Forum – Threads Archive

Update table without using Cursor & Temp Table

Hi All I want to write following update query without using
. DECLARE @col1 as bigint
DECLARE @col2 as bigint
DECLARE @col3 as varchar(10)
DECLARE @col4 as varchar(150) DECLARE updateCur CURSOR
FOR SELECT DISTINCT col1,col2,col3,col4 FROM table1
OPEN updateCur
FETCH NEXT FROM updateCur INTO @col1,@col2,@col3,@col4
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
UPDATE table2
SET [email protected],[email protected],[email protected] WHERE Colid = @col1
END
FETCH NEXT FROM updateCur INTO @col1,@col2,@col3,@col4
END
CLOSE updateCur
DEALLOCATE updateCur
Thanks in Advance…..

---

UPDATE T2
SET T2.Col2 = T1.Col2,
T2.Col3= T1.Col3…..
FROM Table2 T2
INNER JOIN Table1 T1
ON T1.Col1 = T2.col1
Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

---

Thanks Roji… I have "SET STATISTICS IO ON" RESULTS and want to make sure that it best performance possibly achievable… result:
Table ‘T2_________________000100000025’. Scan count 1, logical reads 66, physical reads 0, read-ahead reads 0.
Table ‘T1_________________000100000025’. Scan count 84, logical reads 168, physical reads 0, read-ahead reads 0. I am confuse about logical scan figures..Table have 84 records as it is not much data and there clustered index on T1.Col1 and T2.col1 (in JOIN clause).. Advice and Thanks in Advance.

---

]]>