SQL Server – How to Set Transaction Isolation Level in Table Value Multi Function

You got problem because sqllite and sql server datatype are different.

In the specific, you use a text datatype. This is bad, in sql server, for many reasons:

• Text will be removed in future versions of sql server: https://msdn.microsoft.com/it-it/library/ms187993.aspx
• You cannot compare with the equal operator
• Always choose appropriate data type. Store a number in a text field will lead you to problems.

So, I suggest you to build your table with appropriate datatypes. In your case name must be a varchar and age an integer (if it is ok).

Also you must not learn starting on a book for a specific rdbms and use another one, or you will get a lot of problems like this.

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.