I am passing datatable as input parameter from C# to stored procedure. For that I created user defined table type called EmpType. Datatable contains id, Name,Lname,Mobileno,EmpId. Employee table contains [Name],[Lname],[mobno],[Did] as columns. I want to insert record from datatable to Employee table which are not already present.Here my sql part.
CREATE TYPE [dbo].[EmpType] AS TABLE
(
[Name] [varchar](50) NULL,
[Lname] [varchar](10) NULL,
[MobileNo] [varchar](50) NULL,
[EmpId] [int] NULL
)
Create Procedure Proc_InsertEmpDetails
@tblEmp EmpType READONLY,
@DId int
as
begin
INSERT INTO Employee
([Name],[Lname],[mobno],[Did])
SELECT [Name],[Lname],[mobno] @DId
FROM @tblEmp A
Inner join (
select min(Id) as minID, mobno from @tblEmp group by mobno
) MinIDTbl
on MinIDTbl.minID = A.ExcelId
WHERE NOT EXISTS (SELECT 1
FROM Employee B
WHERE B.[mobno] = A.[mobno]
AND B.[Did] = @DId )
end
It is working properly. I am using sql server 2013.But my developement server is sql server 2000.Sql server 2000 does not support User defined table type.Is there any alternative way to do this?