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