Just give some alias name at the end of below line.
UNION(SELECT tds,sercharge,deve_charges,trimming FROM SetPayout_Master)) mytable
updated sp
CREATE PROCEDURE [dbo].[sp_payout]
(
@sponcorid varchar(50)=null
)
AS
BEGIN
INSERT INTO Payout_Master
SELECT sponcorid,leftcount,rightcount,totcount,pair FROM
((SELECT sponcorid,leftcount,rightcount,totcount,pair FROM TestCounter_Master WHERE sponcorid=@sponcorid
EXCEPT
SELECT tds,sercharge,deve_charges,trimming FROM SetPayout_Master)
UNION(SELECT tds,sercharge,deve_charges,trimming FROM SetPayout_Master)) mytable
END
GO