Esempio di come usare un cursore:
DECLARE @opttype TABLE (code varchar(100),name varchar(300),systemoid varchar(300),description varchar (500),virtual bit,isdigitalized bit,isexternal bit )
insert into @opttype (Code,Name,SystemOid,Description,Virtual,IsDigitalized,IsExternal) values
('18','internal','test','',0,0,0),
('30','internal','test','',0,0,0),
('80','internal','test','',0,0,0),
('81','internal','test','',0,0,0),
('82','internal','test','',0,0,0)
DECLARE @code varchar(100),@name varchar(300),@systemoid varchar(300),@description varchar (500),@virtual bit,@isdigitalized bit,@isexternal bit
DECLARE optype_cursor CURSOR FOR
SELECT code,name,systemoid,description,virtual,isdigitalized,isexternal FROM @opttype
OPEN optype_cursor
FETCH NEXT FROM optype_cursor INTO @code, @name, @systemoid, @description, @virtual, @isdigitalized, @isexternal
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT 1 FROM OperationType WHERE Code = @code and SystemOid=@systemoid)
BEGIN
INSERT INTO OperationType
Code,Name,SystemOid,Description,Virtual,IsDigitalized,IsExternal)
VALUES
(@code, @name, @systemoid, @description, @virtual, @isdigitalized, @isexternal);
END
FETCH NEXT FROM optype_cursor INTO @code, @name, @systemoid, @description, @virtual, @isdigitalized, @isexternal
END
CLOSE optype_cursor
DEALLOCATE optype_cursor