c**t
发帖数: 2744 | 1 like oracle or SQL Server 2008
MERGE INTO TblA USING TblB ON TblA.PK=TblB.PK
WHEN MATCHED THEN UPDATE ..
WHEN NOT MATCHED THEN INSERT ...
how to do it in SQL Server 2000?
Thinking to use cursor of TblB, then
UPDATE TblA set ... WHERE TblA.PK=Cursor.PK
if @@ERROR <> 0 then
INSERT INTO TblA ...
end
This worked, but seems too slow; any better way? | B*****g
发帖数: 34098 | 2 Don't know.
I tried something on oracle
First one seems work:
DECLARE
CURSOR lcurid IS
SELECT a.ID,
b.ID bid,
b.NAME bname
FROM t1 a, t2 b
WHERE a.ID(+) = b.ID
FOR UPDATE OF a.name;
BEGIN
FOR x IN lcurid
LOOP
IF x.id IS NULL THEN
INSERT INTO t1(ID, NAME)
VALUES(x.bid, x.bname);
ELSE
UPDATE t1 c
SET c.name = x.bname
WHERE current of lcurid;
END IF;
END LOOP;
END;
second one not work(error ora-01410:invalid row
【在 c**t 的大作中提到】
: like oracle or SQL Server 2008
: MERGE INTO TblA USING TblB ON TblA.PK=TblB.PK
: WHEN MATCHED THEN UPDATE ..
: WHEN NOT MATCHED THEN INSERT ...
: how to do it in SQL Server 2000?
: Thinking to use cursor of TblB, then
: UPDATE TblA set ... WHERE TblA.PK=Cursor.PK
: if @@ERROR <> 0 then
: INSERT INTO TblA ...
: end
| c**t
发帖数: 2744 | 3 if in oracle why not just simply use merge?
【在 B*****g 的大作中提到】
: Don't know.
: I tried something on oracle
: First one seems work:
: DECLARE
: CURSOR lcurid IS
: SELECT a.ID,
: b.ID bid,
: b.NAME bname
: FROM t1 a, t2 b
: WHERE a.ID(+) = b.ID
| B*****g
发帖数: 34098 | 4 for test only
【在 c**t 的大作中提到】
: if in oracle why not just simply use merge?
| j*****n
发帖数: 1781 | 5 gee, update with inner join, insert with left join...
how difficult job is? | B*****g
发帖数: 34098 | 6 只准scan table一次，哈哈
【在 j*****n 的大作中提到】
: gee, update with inner join, insert with left join...
: how difficult job is?
|
|
