[dba-SQLServer] PostgreSQL Conversion to SQL2012

I have a PostgreSql database that I am converting to SQL 2012.

 

There is a line like this:

 

SELECT insert_listitem_if_missing('FareBucket', paxBucketId, paxBucketName)

FROM dbo.ttmpImportBooking

GROUP BY paxBucketId, paxBucketName

ORDER BY paxBucketId, paxBucketName

 

The insert_listitem_if_missing function checks to see if an item record is
already in a table and inserts it if it is not.  It does it for each record
in the select statement.

 

When I tried to create a similar scalar valued function I received an error
message that I couldn't use an insert in a function.

 

I ended up making the function a stored procedure and using a cursor to loop
through each record and call the stored procedure. 

 

      DECLARE cc3 CURSOR

      FOR SELECT bkagtAgentNo, agtAgentName, agtagyCode

            FROM dbo.ttmpTranzImportBooking

            GROUP BY paxBucketId, paxBucketName

            ORDER BY paxBucketId, paxBucketName

 

      OPEN cc3

      FETCH NEXT FROM cc3 INTO @paxBucketId, @paxBucketName

 

      WHILE @@fetch_status=0

      BEGIN

            EXEC dbo.spInsertListItemMissingShortValue 'FareBucket',
@paxBucketId, @paxBucketName

 

            FETCH NEXT FROM cc3 INTO @paxBucketId, @paxBucketName

      END

 

      CLOSE cc3

      DEALLOCATE cc3  

 

Is there a simpler way to replicate this?

 

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand