Bulk Insert into table using User-Defined Table Type

Bulk Insert into a table using User-Defined Table Type

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

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 a table using a 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