2

I am running an Insert Into statement in my stored procedure that is receiving data from a Table Value Parameter I just realized I need to 1st verify that the data does not already exist in the table before doing an insert so I am attempting to alter my procedure to this

ALTER PROC [dbo].[CMT]
(@TempTable AS dbo.TableValue_ET READONLY)
AS
BEGIN

if b.[empID] NOT IN (Select [empID] FROM [Habib].[dbo].[SE])
BEGIN
  INSERT INTO [Habib].[dbo].[SE]([empID],[POD],[empNotes],[hiredate],[terminationdate],[terminationtime],[address],[city],[state])
  SELECT
  [empID]
  ,[POD]
  ,[empNotes]
  ,convert(varchar(10), [b].[hiredate], 101)
  ,convert(varchar(10), [b].[terminationdate], 101)
  ,[terminationtime] = LTRIM(RIGHT(CONVERT(VARCHAR(20), [b].[terminationdate], 100), 7))
  ,[address]
  ,[city]
  ,[state]
  FROM @TempTable b
END

END

However, when I attempt to save this updated syntax I get an error of

Msg 4104, Level 16, State 1, Procedure UpdateShippingExceptions, Line 7
The multi-part identifier "b.empID" could not be bound.

What must I change so that I can 1st check if the empID already exists in the table before inserting it?

2
  • That needs to be your where clause, not a separate if -- but using exists is probably a better idea
    – James Z
    Jan 23, 2017 at 18:16
  • @JamesZ - how would I use exists? Jan 23, 2017 at 18:18

1 Answer 1

5

The basic form is:

INSERT dbo.table
(
  cols
)
SELECT 
  cols
FROM @tvp AS t
WHERE NOT EXISTS 
(
  SELECT 1 FROM dbo.basetable AS b WHERE b.key = t.key
);

In your case, that would be:

INSERT INTO [Habib].[dbo].[SE]
(
  [empID],
  [POD],
  [empNotes],
  [hiredate],
  [terminationdate],
  [terminationtime],
  [address],
  [city],
  [state] /* why do people prefer horizontal scrollbars to vertical ones? */
)
SELECT 
  [empID],
  [POD],
  [empNotes],
  convert(char(10), [b].[hiredate], 101),
  convert(char(10), [b].[terminationdate], 101),
  LTRIM(RIGHT(CONVERT(VARCHAR(20), [b].[terminationdate], 100), 7)),
  [address],
  [city],
  [state]
FROM @TempTable AS b
WHERE NOT EXISTS
(
  SELECT 1 
    FROM [Habib].[dbo].[SE] AS se
    WHERE se.empID = b.empID
);

One thing you need to find out, though, is if users enter existing empID values here and expect to update existing employees with new information for the other columns... if so, you're throwing their updates away without telling them.

1
  • This is strictly for inserting new only - updates are done in a different way. However, than you for pointing that out. Jan 23, 2017 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.