Row by row operations in sqlserver

hello,

I need to push data from a temporary table into a master table in sql server database(Both tables are in the same database).

I need to follow these conditions.
tables used:
1. temporary table: Temp
2. master table: Master

for every record or row in Temp
check if exists(Temp.field1)in Master.Field1 then
update Master with this row.
else Insert into Master this row.

I performed a research through the net, and found various suggestions. like usage of cursors, usage of while loops etc.
I have to use nearly 50,000 to 60, 000 rows minimum or even more.
Time complexity is also to be considered

I request all who visit this thread to place any possible solutions/suggestions how shall i make this task.

I thank all in advanceYou could eithe ruse a cursor or another loop, like a While LOOP

BUT

i would rather use a setbased operation, because they are in common faster than the Loop or Cursor. So first update every record that exists in the database then insert the data that does not exists in the table.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||Hello,
thanks for your effort, could you tell me a way to first update the data and then do a bulk insert into the master table,

Now im following like this:
1.Populate a table(say tblTemp) using open datasource(an Excel sheet),
2. the tblTemp is created by me on the fly each time the stored procedure(say sp1)
executes,
3. Another stored procedure(say Sp2) performs the update operation followed by Insert
like
while loop through the tblTemp
update the records which matches those in tblMaster.
While ends
"insert into tblMaster([Account Number],[Mobile Number],[Name]) select *
from tblTemp where [Account Number] not in ( select [Account Number]
from tblMaster)".
4. Process ends.
My main concenteration is on the time of execution as i have to manipulate a max of 60,000 or even more records at a time.

Hope you shall get me and please suggest any possible approach.

...thiru|||

OK, assuming that you already have the loaded data in place in the temp table, you will need to have something like a primary or comparison key to find the assoviated records that already exist in the (to be updated) table. With some pseudo SQL code (because you did not provide any DDL that could be something like this (preventing any look or cursor)

CREATE TABLE TempTable
(
ID INT,
Col1 VARCHAR(50),
Col1 VARCHAR(50)
)

UPDATE ToBeUpdatedaTable
SET
Col1 = TT.Col1,
Col2 = TT.Col2
FROM TobeUpdatedTable TU
INNER JOIN TempTable TT
ON TT.ID = TU.ID

INSERt INTO ToBeInsertedTable
(
ID,
Col1,
Col2
)
SELECT

ID,
Col1,
Col2
FROM TempTable TT
WHERE NOT EXISTS
(
SELECT * FROM ToBeInsertedTable TI
INNER JOIN TI.ID = TT.ID
)

HTH, Jens Suessmeyer:

http://www.sqlserver2005.de