UPDATE  bulk_provincia
SET     VALIDO_BULK = 1
WHERE   (numprov IS NOT NULL
         AND idprov IS NOT NULL
         AND numprov <> ''
         AND idprov <> ''
        )
--go
UPDATE  bulk_provincia
SET     operacao_bulk = 'INSERT'
WHERE   numprov IN (SELECT  numprov
                    FROM    bulk_provincia
                    WHERE   valido_bulk = 1
                            AND numprov NOT IN (SELECT  numprov
                                                FROM    provincia))
--go
UPDATE  bulk_provincia
SET     operacao_bulk = 'UPDATE'
WHERE   operacao_bulk IS NULL
        AND valido_bulk = 1
--go
DELETE  FROM bulk_provincia
WHERE   valido_bulk IS NULL
--go
CREATE CLUSTERED INDEX IDX_ORDENA ON bulk_provincia (operacao_bulk)
--go
DECLARE @maxprov AS INT
SET @maxprov = (
                SELECT  MAX(codprov) + 1
                FROM    provincia
               )
EXEC
    ('ALTER TABLE bulk_provincia add CODIGOMAXIMO INT IDENTITY (' + @maxprov
     + ',1)'
    )
--go
UPDATE  bulk_repfiscal
SET     VALIDO_BULK = 1
WHERE   (numrf IS NOT NULL
         AND idrf IS NOT NULL
         AND cod_provincia IS NOT NULL
         AND numrf <> ''
         AND idrf <> ''
         AND cod_provincia <> ''
         AND cod_provincia IN (SELECT   numprov
                               FROM     bulk_provincia
                               UNION
                               SELECT   numprov
                               FROM     provincia)
        )
--go
UPDATE  bulk_repfiscal
SET     operacao_bulk = 'INSERT'
WHERE   numrf IN (SELECT    numrf
                  FROM      bulk_repfiscal
                  WHERE     valido_bulk = 1
                            AND numrf NOT IN (SELECT    numrf
                                              FROM      repfiscal))
--go
UPDATE  bulk_repfiscal
SET     operacao_bulk = 'UPDATE'
WHERE   operacao_bulk IS NULL
        AND valido_bulk = 1
--go
DELETE  FROM bulk_repfiscal
WHERE   valido_bulk IS NULL
--go
UPDATE  bulk_repfiscal
SET     bulk_repfiscal.cod_provincia_bulk = r.codigo
FROM    (
         SELECT codprov AS codigo
               ,numprov AS numero
         FROM   provincia
         UNION
         SELECT codigomaximo AS codigo
               ,numprov AS numero
         FROM   bulk_provincia
         WHERE  valido_bulk = 1
                AND operacao_bulk = 'INSERT'
        ) AS r
WHERE   bulk_repfiscal.cod_provincia = r.numero