Bulk Insert into table using User-Defined Table Type

Bulk Insert into table using User-Defined Table Type

User-defined table type is a user-defined type that represents the definition of a table structure is new feature in SQL 2008. We can use a user-defined table type to declare table-valued parameters for stored procedures or functions, or to declare table variables that you want to use in a batch or in the body of a stored procedure or function.

Create table tblEmployee

CREATE TABLE [dbo].[tblEmployee](
      [EmpID] [int] IDENTITY(1,1) NOT NULL,
      [FirstName] [varchar](50) NULL,
      [LastName] [varchar](50) NULL,
      [Email] [varchar](50) NULL,
      [Address] [varchar](100) NULL,
 )

Create User defined table type typEmployee

CREATE TYPE typEmployee AS TABLE
(
      FirstName VARCHAR(50),
      LastName VARCHAR(50),
      Email VARCHAR(50),
      Address VARCHAR(100)
)

Create Store Procedure usp_InserEmployeeDetail

CREATE PROC usp_InserEmployeeDetail
@typEmployeeDetail      typEmployee ReadOnly
AS
BEGIN
      INSERT INTO tblEmployee(FirstName,LastName,Email,Address)
      SELECT * FROM @typEmployeeDetail
END

Bulk insert into table using user defined table type

--declare typeEmplyee type variable
DECLARE @typEmployee typEmployee 

--insert records into typeEmplyee type variable 

INSERT INTO @typEmployee(FirstName,LastName,Email,Address)
VALUES ('Prakash ','Nayal ','[email protected] ','Merrut')
INSERT INTO @typEmployee(FirstName,LastName,Email,Address)
VALUES ('Rahul ','Porwal ','[email protected] ','Etawa')
INSERT INTO @typEmployee(FirstName,LastName,Email,Address)
VALUES ('Markandy ','Pathak ','[email protected] ','Gorkhpur')
INSERT INTO @typEmployee(FirstName,LastName,Email,Address)
VALUES ('Vishal ','Gupta ','[email protected] ','Merrut')
--pass the typeEmplyee type variable to the store procedure as paramerter
EXEC usp_InserEmployeeDetail  @typEmployee

See the result

SELECT  * FROM tblEmployee