Bulk Insert from multiple text file into a SQL Server Table in Dynamic way:

I have five different text files in my Local Drive(D). Lets try to Insert this all data from text file to below created structure table in SQL Server.

Creating table structure

create table Foo
(
ID varchar(10),
Name varchar(30),
Mobile varchar(10)
)

Creating a Store Procedure to insert all data from different text file to a SQL table

Create procedure [dbo].[Foo_Insert]
as
declare @file varchar(30)
declare @c int=1
declare @n int =5
begin
while (@c<=@n)
begin
EXEC
('
BULK
INSERT Foo
FROM ''D:\SSIS_Work\Foo'+@c+'.txt''
WITH
(
FIELDTERMINATOR = ''\t'',
ROWTERMINATOR = ''\n'')'
)
set @c=@c+1
end
end

Now execute the SP
Exec [Foo_Insert]