NqBzWBaM

1create table x ( y int not null primary key )
2
3begin transaction
4insert into x(y)
5values(1)
6insert into x(y)
7values(2)
8insert into x(y)
9values(2)
10insert into x(y)
11values(3)
12commit
13	
14INSERT INTO X VALUES(Y,Z)    WHERE Y  NOT IN (SELECT Y FROM X)
15INSERT INTO X2 VALUES(Y2,Z2) WHERE Y2 NOT IN (SELECT Y FROM X2)
16INSERT INTO X3 VALUES(Y3,Z3) WHERE Y3 NOT IN (SELECT Y FROM X3)
17	
18BEGIN TRY
19    INSERT ...
20END TRY
21BEGIN CATCH /*required, but you dont have to do anything */ END CATCH
22...
23	
24SET XACT_ABORT OFF ; -- > really important to set that to OFF
25BEGIN
26DECLARE @Any_error int
27DECLARE @SSQL varchar(4000)
28BEGIN TRANSACTION
29    INSERT INTO Table1(Value1) VALUES('Value1')
30    SELECT @Any_error = @@ERROR
31    IF @Any_error<> 0 AND @Any_error<>2627 GOTO ErrorHandler
32
33    INSERT INTO Table1(Value1) VALUES('Value1')
34    SELECT @Any_error = @@ERROR
35    IF @Any_error<> 0 AND @Any_error<>2627 GOTO ErrorHandler
36
37    INSERT INTO Table1(Value1) VALUES('Value2')
38    SELECT @Any_error = @@ERROR
39    IF @Any_error<> 0 AND @Any_error<>2627 GOTO ErrorHandler
40
41    ErrorHandler: 
42       IF @Any_error = 0 OR @Any_error=2627
43       BEGIN 
44           PRINT @ssql 
45           COMMIT TRAN
46       END
47       ELSE 
48       BEGIN 
49           PRINT @ssql 
50           ROLLBACK TRAN 
51       END
52END
53	
54INSERT INTO KeyedTable(KeyField, Otherfield)
55SELECT n.* FROM 
56    (SELECT 'PossibleDupeLiteral' AS KeyField, 'OtherfieldValue' AS Otherfield
57     UNION ALL
58     SELECT 'PossibleDupeLiteral', 'OtherfieldValue2'
59    )
60LEFT JOIN KeyedTable k
61    ON k.KeyField=n.KeyField
62WHERE k.KeyField IS NULL
63	
64INSERT INTO t1 (ID, NAME)
65      SELECT valueid, valuename
66      WHERE  NOT EXISTS
67               (SELECT 0
68                FROM   t1 as t2
69                WHERE  t2.ID = valueid AND t2.name = valuename)
70	
71create table X( col1.....)
72
73CONSTRAINT [pk_X] PRIMARY KEY CLUSTERED 
74(
75)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 70) ON [PRIMARY]
76) ON [PRIMARY]
77	
78DECLARE @RoleToAdds TABLE
79([RoleID] int, [PageID] int)
80
81INSERT INTO @RoleToAdds ([RoleID], [PageID])
82    VALUES
83    (1, 2),
84    (1, 3),
85    (1, 4),
86    (2, 5)
87
88INSERT INTO [dbo].[RolePages] ([RoleID], [PageID])
89    SELECT rta.[RoleID], rta.[PageID] FROM @RoleToAdds rta WHERE NOT EXISTS 
90        (SELECT * FROM [RolePages] rp WHERE rp.PageID = rta.PageID AND rp.RoleID = rta.RoleID)