If we use bulk insert, upfront we need to create table with required columns. Here is the solution, it will create table with with required columns and load the data into table

CREATE PROCEDURE dbo.dynamic_table_loading

(

@file_path varchar(1024),

@table_name varchar(256),

@file_type varchar(100)='tab',

@column_length varchar(100)='1024',

@debug bit=0

)

AS

/****************************************************

USE:- This stored procedure is used to load datafrom given file path to

given table name.This stored procedure will automatically create required table with columns

(If we use bulk insert, we need to know number of columns in flat file, but using this stored procedure

we it will create table with with required columns and load the data into table)

PARAMETERS:

@file_path:-full path of the file

@table_name:-table name(it will drop this table, if exists with this name)

@file_type:-type of the file(default tab deliminated) (or)',' (or)'|'

@column_length=lengh of the all the columns in table(default is 1024)

@debug=default is 0 :0 it will not print any messages)

:1 it will print all the messages generated by storedproc(use while debugging storedproc)

EXEC dbo.dynamic_table_loading

@file_path='C:\test.xls' ,

@table_name='testing' ,

@file_type=',',

@column_length='4000',

@debug=1

*******************************************************/

BEGIN

BEGIN TRY

SET NOCOUNT ON

declare @procName varchar(100), @dbName varchar(256), @date varchar(20), @ntab varchar(200),

@nln varchar(4), @rows varchar(10)

declare @ErrMsg nvarchar(4000), @ErrSev varchar(3), @ErrSt varchar(4), @ErrProc nvarchar(126), @ErrLn nvarchar(10),

@ErrNr nvarchar(10), @ErrLoc nvarchar(300)

select @procName=DB_NAME()+'.'+ss.name+'.'+so.name from sys.objects so, sys.schemas ss

where so.object_id = @@PROCID and so.schema_id=ss.schema_id

select @date = cast(getDate() as varchar(20)),@nln=''+char(10)+' ', @dbName=DB_NAME(),

@ntab=@procName+'> '

DECLARE @bulkCmd varchar(4000),@SQL VARCHAR(1024),@tableSql varchar(1024),

@replace varchar(100),@last_column varchar(100)

if @debug=1

raiserror('Entering procedure "%s"with filepath%s,table_name:%s and file_type',0,1,@file_path,@table_name,@file_type)with nowait

---if file_type tab deliminated , it will replace tab with char(9)

if @file_type='tab'

set @file_type=CHAR(9)

SET @replace='] varchar('+@column_length+'), ['

IF OBJECT_ID(N'tempdb..#temp') IS NOT NULL

drop table #temp

create table #temp (line varchar(max))

SET @SQL='IF OBJECT_ID('''+@table_name+''') IS NOT NULL

drop table '+@table_name+''

if @debug=1

print @SQL

EXEC(@SQL)

SET @bulkCmd='bulk insert #temp from '''+@file_path +''' with (FIRSTROW=1,LASTROW=1)'

if @debug=1

print @bulkCmd

EXEC (@bulkCmd)

--build the temporary table dynamically, yet create it with static sql.

SET @SQL='create table '+@table_name+'( placeHolder int)'

if @debug=1

print @SQL

EXEC(@SQL)

select top 1 @tableSql='alter table '+@table_name+' add ['+LIne from #temp

if @debug=1

Print @tableSql

set @tableSql = Replace(@tableSql, @file_type, @replace)

if @debug=1

Print @tableSql

set @tableSql = @tableSql + '] varchar('+@column_length+')'

if @debug=1

Print @tableSql

EXEC (@tableSql)

SET @SQL='alter table '+@table_name+' drop column placeHolder'

if @debug=1

print @SQL

EXEC(@SQL)

SET @bulkCmd='bulk insert '+@table_name+' from '''+@file_path +'''

with( FIRSTROW=2,

fieldterminator ='''+@file_type+''',

rowterminator = '''+char(10)+''' )'

if @debug=1

print @bulkCmd

EXEC (@bulkCmd)

if @debug=1

raiserror('input file %s successfully loaded into table %s',0,1,@file_path, @table_name)

select @last_column=QUOTENAME(name) from sys.columns where object_id =

object_id(@table_name) and column_id=(select max(column_id)

from sys.columns where object_id =object_id(@table_name))

if @debug=1

raiserror('updating last column %s in table:%s,if it has any special characters ',0,1,@last_column, @table_name)

SET @SQL='UPDATE '+@table_name+' set '+@last_column+'=ltrim(rtrim(rEPLACE(REPLACE(REPLACE('+@last_column+', CHAR(10), ''''), CHAR(13), ''''), CHAR(9), '''')))'

if @debug=1

print @SQL

EXEC(@SQL)

if @debug=1

raiserror(' Leaving procedure %s at %s', 0, 1, @procName, @date) with nowait

END TRY

BEGIN CATCH

select @ErrMsg =ERROR_MESSAGE(), @ErrSev =ERROR_SEVERITY(), @ErrSt =ERROR_STATE(),

@ErrProc =ERROR_PROCEDURE(), @ErrLn = ERROR_LINE(), @ErrNr = ERROR_NUMBER()

raiserror('Failed to execute %s,procname %s Error nr %s, state %s, sev %s; line %s, %s with msg: %s',

16,-1,@procName, @nln,@ErrNr,@ErrSt,@ErrSev,@ErrLn,@nln,@ErrMsg)

END CATCH

END

GO