Click here to Skip to main content
15,113,057 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
SQL
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?
Posted
Updated 27-Apr-15 2:44am
v2
Comments
Suvendu Shekhar Giri 27-Apr-15 7:47am
   
SQL Server 2013?? Where did you get that?
SQL Server Release History
virusstorm 27-Apr-15 8:59am
   
First of all, if you are truly using SQL Server 2000 (which is 15 years old) for development, you need to upgrade. In most local development cases, SQL Server Express is more then sufficient.

Second, like Suvendu pointed out, you may want to double check your version numbers as SQL Server 2013 never existed.
Member 11589429 28-Apr-15 0:48am
   
I am using vs 2013 and sql server 2012.
Herman<T>.Instance 28-Apr-15 4:39am
   
Since a database is row based, you better iterate your datatable row by row and CRUD per row

   
Comments
Member 11589429 29-Apr-15 1:34am
   
Hello Rajat,I know how to make user defined table type.My question is not like that....
Rajat-Indiandotnet 29-Apr-15 2:08am
   
Sorry,
I missed SQL SERVER 2000.

Other solution which you can do is call BCP query to insert in bulk.

TVP's are a thing of beauty in c#[^]

(Unfortunately, I had to impl them in OLEDB and ODBC ...)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900