Bulk Insert data into table without using loop.

Here i have comma separated string like '1,2,3,4', and i want to this value insert into table without using loop. To reduce the functionality of loop during bulk insertion i have used a table variable to insert the value in the table.

Here i am declaring Employee table:

Declare @Employee Table(EmployeeID int identity,EmployeeName nvarchar(50),Role int)

And also role table:

Declare @Role Table(RoleID int identity,Role int)

Now i am inserting role in Role Table:

Declare @ID nvarchar(50)='1,2,3,4,5'Declare @Role Table(RoleID int identity,Role int)

Insert Into @Role(Role)SELECT splitdata FROM fnSplitString(@ID,',')

Print 'Values in @Role'Select * From @Role

In Above i used a function 'fnSplitString' for split the commo separated data into the table.

Below you can see the value inserted into the Role table.

Now i am inserting role into the Employee Table using of Bulk Insertion as follows:

Insert Into @Employee(EmployeeName,Role) Select @EmployeeName,Role From @Role

Execute below all code :

Declare @ID nvarchar(50)='1,2,3,4,5'Declare @Role Table(RoleID int identity,Role int)
Insert Into @Role(Role)SELECT splitdata FROM fnSplitString(@ID,',')
--Print 'Values in @Role'--Select * From @Role

Declare @EmployeeName nvarchar(50)='Ajay Gangwar'Declare @Employee Table(EmployeeID int identity,EmployeeName nvarchar(50),Role int)
Insert Into @Employee(EmployeeName,Role) Select @EmployeeName,Role From @Role
Print 'Values in @Employee'Select * From @Employee

Below you can see the value inserted into the Employee table.

This is done functionality of bulk insertion without using loop.