How to restrict insertion of value that already exists in other table

I have 2 tables. I need to check before insertion in one table if the valu开发者_运维百科e exist in other table.

I suggest that, you should first check the records that is going to be inserted in each request.

Create Proc Testing
as
Set NoCount ON
Set XACT_ABORT ON

Begin Try
   Begin Tran
    IF Not Exists(SELECT 1 FROM Table2 i JOIN Table1 t ON i.key = t.key)
    Begin
         //Your insert statement
    END
   Commit Tran
End Try

Begin Catch
    Rollback Tran
End Catch

IF NOT EXISTS ( SELECT * FROM TableA WHERE Col1 = @Value) INSERT INTO TableB(Col1) SELECT @Value

Using an INSERT trigger perhaps?

I'm not very sure about the syntax.

CREATE TRIGGER InsertTableTrigger ON Table1 FOR INSERT
 AS
  BEGIN
    IF EXISTS ( SELECT 1 FROM Inserted i JOIN Table1 t ON i.key = t.key )
    BEGIN
        RAISERROR('Transaction Failed.',16,1)
        ROLLBACK TRAN "insert on Table1"
    END
  END
 GO

Inserted is used to access the inserting values.