What is wrong with my stored procedure?

Question

I need to insert the selected data from tblA to tblB only if data selected does not exist in tblB. I created a button that will execute this stored procedure.
I cannot figure out what I am doing wrong in my stored procedure, it is not inserting/showing non existing data into tblB.

I am using SQL Server 2008 and ASP.NET.

CREATE PROCEDURE [dbo].[ADDATA]
@EmpFrom varchar(7) 
,@EmpTo varchar(7)   
AS
SET NOCOUNT ON;
DECLARE @affectedRows int
SET @affectedRows = 0;
BEGIN 
    IF NOT EXISTS (SELECT 1 FROM [dbo].[tblA] WHERE @EmpFrom = @EmpTo) 
    SET @affectedRows = @affectedRows + @@ROWCOUNT

BEGIN
INSERT INTO tblB
(EmpNum --- PK
 ,Last_First
 ,Title
 ,NTUserName)
select
@EmpTo
,a.emp_name_lfn
,a.job_title
,a.[user_id]
FROM tblA 
    END
END

Answer 1

The variable @EmpToUpdateTo is not declared. You'll need to make that a valid variable before this will work.

Also, the following line:

IF NOT EXISTS (SELECT 1 FROM [dbo].[tblA] WHERE @EmpFrom = @EmpTo)

Is essentially saying IF (@EmpFrom <> $EmpTo). I encourage you to use the latter--it's faster.

What's the intent of this, though? It looks like you're trying to insert all of the rows that aren't in tblB but are in tblA into tblB. This query actually has no bearing on what @EmpFrom or @EmpTo are, just so long as they aren't equal.

Answer 2

I am not 100% certain I know what you are after. That said, if you are trying to insert the values tied to @EmpFrom in tblA into tblB only if there isn't a value in tblB represented by @EmpTo, I think the following should do that for you.

You will noticed I cleaned up a few things from your routine. Since I didn't see the purpose of the @affectedRow setter, I got rid of it. Also, your table aliases were missing and I think your BEGIN and END statements are probably causing you some grief as you had the @affectRows setters immediately after the IF but not within the BEGIN.

Now, my guess is that EmpNum is not only the primary key but an identity seed. If you are looking to return this new EmpNum from tblB, it is probably best to just selec the last inserted tblB row as I did in the script below. Alternatively, you could return just the new value using Select @@identity.

Gosh, I hope I understand your question correctly. Best of luck.

CREATE PROCEDURE [dbo].[ADDATA]
   @EmpFrom varchar(7)  --ddlA,
   @EmpTo varchar(7)   --ddlB
AS 

SET NOCOUNT ON;

DECLARE @affectedRows int;
SET @affectedRows = 0;

IF NOT EXISTS (SELECT 1 FROM [dbo].[tblB] WHERE EmpNum = @EmpTo) --ddl key id    

BEGIN
   INSERT INTO tblB (Last_First, Title, NTUserName)
   SELECT a.emp_name_lfn, a.job_title, a.[user_id]
   FROM tblA a    
   WHERE a.EmpNum = @EmpFrom

   SELECT EmpNum, Last_First, Title, NTUserName
   FROM tblB 
   WHERE EmpNum = @@identity 
END

Answer 3

SQL Server 2008 has the MERGE statement which will allow you to update, insert, and delete data all in one statement. In your case you can use it to insert data that doesn't exist in tblB but exists in tblA. The syntax is as follows:

MERGE tblB AS Target
USING
(
SELECT EmpNum ,Last_First ,Title ,NTUserName
FROM tblA
) AS Source
ON (Source.EmpNum = Target.EmpNum)
-- Empnum exists in source and target, update all fields from source to target
WHEN MATCHED THEN
UPDATE SET
Target.Last_First = Source.Last_First,
Target.Title = Source.Title,
Target.NTUserName = Source.NTUserName
-- No records exist in target table, insert them from source
WHEN NOT MATCHED
INSERT (EmpNum ,Last_First ,Title ,NTUserName)
VALUES(Source.EmpNum, Source.Last_First, Source.Title, Source.NTUserName)

Answer 4

Try it this way

CREATE PROCEDURE [dbo].[ADDATA]

@EmpTo varchar(7)
AS SET NOCOUNT ON; DECLARE @affectedRows int SET @affectedRows = 0; BEGIN
IF NOT EXISTS (SELECT * FROM [dbo].[tblB] WHERE EmpNum = @EmpTo) BEGIN INSERT INTO tblB(EmpNum, Last_First, Title, NTUserName) SELECT @EmpTo, emp_name_lfn, job_title, [user_id] FROM tblA WHERE [Whatever_The_EmpNum_Field_Is_Named_In_tblA] = @EmpTo SET @affectedRows = @affectedRows + @@ROWCOUNT END END

Hope this helps, ~ck