how to check if the record exists before insert to prevent duplicates?

The first, and most important, issue here is not that your query inserts duplicates; it is that your table allows it to happen in the first place. Hence, you first need to create a UNIQUE INDEX on those 3 fields to disallow duplicates.

The second issue is how to handle the situation of when an operation attempts to insert a duplicate. You have two main choices:

1. You can check for the record's existence first and skip the INSERT if it is found, or

2. You can set the UNIQUE INDEX to "ignore" duplicates in which case you don't need to check first as the operation will silently fail, with just a warning that the duplicate was not inserted.

---

If you pick Option #1 (check first), then:

CREATE UNIQUE NONCLUSTERED INDEX [UIX_tblOrder_CustomerID_OrderTypeID_LoanNumber]
    ON [tblOrder]
    ( [CustomerID] ASC, [OrderTypeID] ASC, [LoanNumber] ASC );

And then:

INSERT INTO tblOrder (CustomerID, OrderTypeID, LoanNumber)
  SELECT o.CustomerID, 3, o.LoanNumber
  FROM   tblOrder as o
  WHERE  o.OrderTypeID = 1
  AND    NOT EXISTS (SELECT *
                     FROM tblOrder tmp
                     WHERE tmp.CustomerID = o.CustomerID
                     AND   tmp.OrderTypeID = 3
                     AND   tmp.LoanNumber = o.LoanNumber);

If you pick Option #2 (don't check), then:

CREATE UNIQUE NONCLUSTERED INDEX [UIX_tblOrder_CustomerID_OrderTypeID_LoanNumber]
    ON [tblOrder]
    ( [CustomerID] ASC, [OrderTypeID] ASC, [LoanNumber] ASC )
    WITH (IGNORE_DUP_KEY = ON);

And then:

INSERT INTO tblOrder (CustomerID, OrderTypeID, LoanNumber)
  SELECT o.CustomerID, 3, o.LoanNumber
  FROM   tblOrder as o
  WHERE  o.OrderTypeID = 1;

---

Example of how IGNORE_DUP_KEY behaves:

CREATE TABLE #IgnoreDuplicateTest (Col1 INT);
CREATE UNIQUE NONCLUSTERED INDEX [UIX_#IgnoreDuplicateTest_Col1]
    ON #IgnoreDuplicateTest
    ( [Col1] ASC )
    WITH (IGNORE_DUP_KEY = ON);

INSERT INTO #IgnoreDuplicateTest (Col1) VALUES (1);
-- (1 row(s) affected)

INSERT INTO #IgnoreDuplicateTest (Col1) VALUES (1);
-- Duplicate key was ignored.
-- (0 row(s) affected)

INSERT INTO #IgnoreDuplicateTest (Col1)
 SELECT tmp.val
 FROM (VALUES (1),(2)) AS tmp(val);
-- Duplicate key was ignored.
-- (1 row(s) affected)

SELECT * FROM #IgnoreDuplicateTest;

-- Col1
--    1
--    2

this may helps you

Here I am checking the values present(which are in variables) are present in table or not, if present leave else insert

 Declare @claimid int, @subscriber int, @qualifyinginformation int
set @claimid = '1000008'
set @subscriber = '1'
set @qualifyinginformation = '1'

If Exists (Select * from test1 where claimid = @claimid and subscriber=@subscriber and qualifyinginformation=@qualifyinginformation )
begin
     print ('The Value already Exist')
 end
else
 begin
   Insert into test1 (claimid,subscriber,qualifyinginformation) values (@claimid,@subscriber,@qualifyinginformation)
select * from test1
end

Is this what you want?

CREATE TABLE tblOrder(
    OrderID     INT IDENTITY(1, 1),
    CustomerID  INT,
    OrderTypeID INT,
    LoanNumber  INT
)
INSERT INTO tblOrder VALUES
(1, 1, 45584565),
(1, 1, 45566856),
(1, 1, 45565584),
(1, 1, 45588545)

INSERT INTO tblOrder( CustomerID, OrderTypeID, LoanNumber)
SELECT
    o.CustomerID,
    3,
    o.LoanNumber
FROM tblOrder o
WHERE NOT EXISTS(
    SELECT 1
    FROM tblOrder
    WHERE
        CustomerID = o.CustomerID
        AND OrderTypeID = 3
        AND LoanNumber = o.LoanNumber
)