Does the RowID needs to be arbitrary? If not, then you can add an IDENTITY column to your destination table and leave the complexity for SQL Server to handle.