Your posting has a large number of fundamental errors. The first is that you didn't bother to post correct DDL; by definition, a table must have a key. That should of been covered the first week of your Database class. That same week, they would've told you the differences between rows and records. And that since SQL is declarative, there is no looping in the language.

Identifiers cannot be integers because you don't do math with them. We do not use assembly language BIT flags in SQL. Currency amounts are done with the decimal data type. The names "source" and "target" are metadata that describe how the table is used, not what it is. Can you get a book on basic data modeling or if you really want to make yourself suffer read the metadata standards? When you get that book on data modeling, you'll learn the status is from the Latin for "a state of being" and it must have a temporal dimension, shown as a start and stop timestamp for when that status was valid.

I am going to make some guesses and try and correct what you did

CREATE TABLE Vendors
(vendor_duns CHAR(9) NOT NULL PRIMARY KEY,
vendor_name VARCHAR(35) NOT NULL,
broker_fee DECIMAL (12,2) NOT NULL
CHECK(broker_fee >= 0.00));

Read Chris Date and what he has said about tables with the same structure as a huge design error. This target table should not exists at all. I’m going to guess that what you’re trying to do is recorded history of the vendors on some status variable that we have no idea about

CREATE TABLE Vendor_History
(vendor_duns CHAR(9) NOT NULL
REFERENCES Vendors(vendor_duns),
something_start_date DATE NOT NULL,
something_end_date DATE,
CHECK (something_start_date <= something_end_date),
something_status CHAR(5) NOT NULL
CHECK (something_status IN (..))
);

Now design your status encoding and let the DRI handle everything for you. There are some other idioms to keep the intervals from overlapping and gasping. You can Google the code.

>> please let me know using recursive CTE or cursor or while loop example <<

Why would you ever think of something that complicated?