Z4MaKCmc

1CREATE PROCEDURE [dbo].[ADDATA]
2@EmpFrom varchar(7) 
3,@EmpTo varchar(7)   
4AS
5SET NOCOUNT ON;
6DECLARE @affectedRows int
7SET @affectedRows = 0;
8BEGIN 
9    IF NOT EXISTS (SELECT 1 FROM [dbo].[tblA] WHERE @EmpFrom = @EmpTo) 
10    SET @affectedRows = @affectedRows + @@ROWCOUNT
11
12BEGIN
13INSERT INTO tblB
14(EmpNum --- PK
15 ,Last_First
16 ,Title
17 ,NTUserName)
18select
19@EmpTo
20,a.emp_name_lfn
21,a.job_title
22,a.[user_id]
23FROM tblA 
24    END
25END
26	
27CREATE PROCEDURE [dbo].[ADDATA]
28	
29MERGE tblB AS Target
30USING
31(
32SELECT EmpNum ,Last_First ,Title ,NTUserName
33FROM tblA
34) AS Source
35ON (Source.EmpNum = Target.EmpNum)
36-- Empnum exists in source and target, update all fields from source to target
37WHEN MATCHED THEN
38UPDATE SET
39Target.Last_First = Source.Last_First,
40Target.Title = Source.Title,
41Target.NTUserName = Source.NTUserName
42-- No records exist in target table, insert them from source
43WHEN NOT MATCHED
44INSERT (EmpNum ,Last_First ,Title ,NTUserName)
45VALUES(Source.EmpNum, Source.Last_First, Source.Title, Source.NTUserName)
46	
47IF NOT EXISTS (SELECT 1 FROM [dbo].[tblA] WHERE @EmpFrom = @EmpTo)
48	
49CREATE PROCEDURE [dbo].[ADDATA]
50   @EmpFrom varchar(7)  --ddlA,
51   @EmpTo varchar(7)   --ddlB
52AS 
53
54SET NOCOUNT ON;
55
56DECLARE @affectedRows int;
57SET @affectedRows = 0;
58
59IF NOT EXISTS (SELECT 1 FROM [dbo].[tblB] WHERE EmpNum = @EmpTo) --ddl key id    
60
61BEGIN
62   INSERT INTO tblB (Last_First, Title, NTUserName)
63   SELECT a.emp_name_lfn, a.job_title, a.[user_id]
64   FROM tblA a    
65   WHERE a.EmpNum = @EmpFrom
66
67   SELECT EmpNum, Last_First, Title, NTUserName
68   FROM tblB 
69   WHERE EmpNum = @@identity 
70END