Data Migration Script - Execute Stored Procedure Set-based

I am writing a number of data migration scripts, like the (simplified version) below

Code:
INSERT INTO newDatabase.dbo.DaDestinationTable(col1, col2, col3)
SELECT col1,
col27,
col13
FROM OldDatabase.dbo.DaSourceTable

There are no validation rules defined in the database (don't ask why), only in the program code. Because of the absence of validations in the database, the data migration scripts could violate business rules (NOT NULL, FK violations, domain values in a column, ...).

A solution could be that both the program and the data migration call the same stored procedure to insert records in the destination table, so the validation rules must only be defined in one place (the stored procedure).

I have never used stored procedures in a "set-based" situation. Is this even possible? The only solution I see is to call those sp's in a loop (WHILE or CURSOR). Something like

Code:
SET @my_id = -1
SELECT @my_id = MIN(id)
FROM OldDatabase.dbo.DaSourceTable
WHERE id > @my_id
WHILE @my_id IS NOT NULL

[Code] ....

For each record, the table OldDatabase.dbo.DaSourceTable is hit twice, once to get the next id (@my_id), once to get the column values. I could combine both with one SELECT script by using ROW_NUMBER(), but I doubt that that will perform faster.

I only work set-based, I have barely ever used a WHILE loop, let alone a CURSOR. What the consequences will be on performance. There are millions of records that have to be migrated.

I was thinking of CROSS APPLY, but you can't use that in combination with a SP.