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 ReadOnlyASBEGIN      INSERT INTO tblEmployee(FirstName,LastName,Email,Address)      SELECT * FROM @typEmployeeDetailEND

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 ','prakahn@gmail.com ','Merrut')

INSERT INTO @typEmployee(FirstName,LastName,Email,Address)VALUES ('Rahul ','Porwal ','rahul@gmail.com ','Etawa')

INSERT INTO @typEmployee(FirstName,LastName,Email,Address)VALUES ('Markandy ','Pathak ','markandey@gmail.com ','Gorkhpur')

INSERT INTO @typEmployee(FirstName,LastName,Email,Address)VALUES ('Vishal ','Gupta ','vishalg@gmail.com ','Merrut')

--pass the typeEmplyee type variable to the store procedure as paramerter

EXEC usp_InserEmployeeDetail  @typEmployee

See the result

SELECT  * FROM tblEmployee