How to insert a data table into SQL Server database table

How to insert a data table into SQL Server database table

Table of Contents

1. Create a User-Defined TableType in your db.
2. Create Stored Procedure and define the parameters.


Create a User-Defined TableType in your db.

/****** Object:  UserDefinedTableType [dbo].[MapExcelTableType]    Script Date: 07/22/2014 14:57:05 ******/
CREATE TYPE [dbo].[MapExcelTableType] AS TABLE(
      [ID] [int] NULL,
      [CostCenter] [varchar](150) NULL,
      [MobileNo] [varchar](150) NULL,
      [EmailID] [varchar](150) NULL,
      [FirstName] [varchar](150) NULL,
      [LastName] [varchar](150) NULL,
      [Services] [varchar](150) NULL,
      [UsageType] [varchar](150) NULL,
      [Network] [varchar](150) NULL,
      [UsageIncluded] [int] NULL,
      [Unit] [varchar](150) NULL
)
GO


Create Stored Procedure and define the parameters.

ALTER PROCEDURE [dbo].[SubscriberServiceMappingByExcel]
      @TableData MapExcelTableType Readonly,
      @CompanyID int,
      @TenantID int
AS
BEGIN
     
SET NOCOUNT ON
   
DECLARE @ID int, @CostCenter varchar(150), @MobileNo varchar(150), @EmailID varchar(150), @FirstName varchar(150), @LastName varchar(150),@Services varchar(150),@UsageType varchar(150), @Network varchar(150), @UsageIncluded int, @Unit varchar(150)
                 
DECLARE @TempTable TABLE (ID int,CostCenter varchar(150), MobileNo varchar(150), EmailID varchar(150), FirstName varchar(150), LastName varchar(150),Services varchar(150),UsageType varchar(150), Network varchar(150), UsageIncluded int, Unit varchar(150))
   
DECLARE _cursor CURSOR FOR
SELECT ID, CostCenter, MobileNo, EmailID, FirstName, LastName, Services, UsageType, Network, UsageIncluded, Unit FROM @TableData
     
OPEN _cursor
FETCH NEXT FROM _cursor
           INTO @ID,
                @CostCenter,
                  @MobileNo,
                  @EmailID,
                  @FirstName,
                  @LastName,
                  @Services,
                  @UsageType,
                  @Network,
                  @UsageIncluded,
                  @Unit
                 
 WHILE @@FETCH_STATUS = 0
   BEGIN
                      
      Insert Into [Subscriber](
                               EmailID
                                    ,MobileNo
                                    ,FirstName
                                    ,LastName
                                    ,CompanyID
                                    ,TenantID
                                    ,StatusID
                                    ,CostCenterID
                              )
                              values
                              (
                                     @EmailID
                                    ,@MobileNo
                                    ,@FirstName
                                    ,@LastName
                                    ,@CompanyID
                                    ,@TenantID
                                    ,3
                                    ,@CostCenter
                              )
                        END  
                
      FETCH NEXT FROM _cursor 
                 INTO @ID, @CostCenter,        @MobileNo, EmailID@FirstName,@LastName,@Services,@UsageType,@Network,
@UsageIncluded,@Unit
      END
      CLOSE _cursor 
      DEALLOCATE _cursor
     
      IF(@@ERROR = 0)
            SELECT 1
      ELSE
            SELECT 1
END

using c# .net, send the DataTable data to sql server.

public string SaveExcelData(DataTable excelTable, int CompanyID, int TenantID)
{
    string strReturnValue = "";
            try
            {
                SqlHelper helper = new SqlHelper();
                helper.OpenConnection();

                ArrayList sqlParameter = new ArrayList();
                object returnValue = "";

                var param = new SqlParameter("@TableData", SqlDbType.Structured);
                param.TypeName = "dbo.MapExcelTableType";
                param.Value = excelTable;

                sqlParameter.Add(param);

                sqlParameter.Add(new SqlParameter("@CompanyID", CompanyID));
                sqlParameter.Add(new SqlParameter("@TenantID", TenantID));

                returnValue = helper.ExecuteScalar("SubscriberServiceMappingByExcel", CommandType.StoredProcedure, sqlParameter);    

                try
                {
                    strReturnValue = returnValue.ToString();
                }
                catch (Exception ex)
                { }
            }
            catch (Exception ex)
            {
                strReturnValue = "error";
            }
            return strReturnValue;
 }
ANIL SINGH

Hey! I'm Anil Singh. I author this blog. I'm Active Blogger, Programmer. I love learning new technologies, programming, blogging and participating the forum discussions more...
My Blogs - http://www.code-sample.com and http://www.code-sample.xyz
My Books - Google Amazon and Flipkart Book Store!

You Might Also Like
Post a Comment
www.code-sample.com/. Powered by Blogger.
ASK Questions
SQL Server NodeJs TypeScript JavaScript Angular SQL Server My Book