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