Load data from text file with variable columns
This script loads from a formatted text file (E.g. tab delimited) with variable number of columns using BULK INSERT. For instance, the file may have any number of columns and next load (seconds later) the same file may have a different number of columns.
This script reads the first line (column names) extracting the names and using them to create a table dynamically appending the time stamp string to the table name (to ensure uniqueness). After executing the CREATE TABLE directive, and creating a new table, it will execute the second BULK INSERT to load the data to the new table.
The table definition uses varchar(1000) for all the columns to make the load easier. I would suggest to perform data validation the load is complete.
create table ##tbl (line varchar(1000))
bulk insert ##tbl
from '\\[computername]\c$\tbl.txt'
with (FIRSTROW=1)
--select top 1 Replace(Line, char(9), ',') from ##tbl
declare @tempstr varchar(1000)
declare @col varchar(1000)
declare @createtbl varchar (1000)
declare @loadtbl varchar (1000)
declare @newtblname varchar(1000)
/* ====================================================================
Generates a unique identifier for all the Event Log entries
====================================================================*/
declare @month char(2)
declare @date char(2)
declare @year char(4)
declare @hour char(2)
declare @min char(2)
declare @sec char(2)
declare @msec char(3)
set @month = DATEPART(m, getdate())
if LEN(RTRIM(DATEPART(m, getdate()))) = 1
set @month = '0'+RTRIM(DATEPART(m, getdate()))
set @date = DATEPART(d, getdate())
if LEN(RTRIM(DATEPART(d, getdate()))) = 1
set @date = '0'+RTRIM(DATEPART(d, getdate()))
set @year = DATEPART(yyyy, getdate())
set @hour = DATEPART(hh, getdate())
if LEN(RTRIM(DATEPART(hh, getdate()))) < 2
set @hour = '0'+RTRIM(DATEPART(hh, getdate()))
set @min = DATEPART(mi, getdate())
if LEN(RTRIM(DATEPART(mi, getdate()))) < 2
set @min = '0'+RTRIM(DATEPART(mi, getdate()))
set @sec = DATEPART(ss, getdate())
if LEN(RTRIM(DATEPART(ss, getdate()))) < 2
set @sec = '0'+RTRIM(DATEPART(ss, getdate()))
set @msec = DATEPART(ms, getdate())
if LEN(RTRIM(DATEPART(ms, getdate()))) < 3
begin
if LEN(RTRIM(DATEPART(ms, getdate()))) = 2
set @msec = '0'+RTRIM(DATEPART(ms, getdate()))
if LEN(RTRIM(DATEPART(ms, getdate()))) = 1
set @msec = '00'+RTRIM(DATEPART(ms, getdate()))
end
set @col = ''
set @tempstr = (select top 1 RTRIM(Replace(Line, char(9), ',')) from ##tbl)
while CHARINDEX(',',@tempstr) > 0
begin
set @col = @col+SUBSTRING(@tempstr, 1, CHARINDEX(',',@tempstr)-1)+' varchar(1000),'
set @tempstr = SUBSTRING(@tempstr, CHARINDEX(',',@tempstr)+1, len(@tempstr))
end
set @col = @col+@tempstr+' varchar(1000))'
set @createtbl = 'create table load_'+RTRIM(@month+@date+@year+@hour+@min+@sec)+' ('+@col
set @newtblname = 'load_'+RTRIM(@month+@date+@year+@hour+@min+@sec)
--select @createtbl
drop table ##tbl
exec (@createtbl)
--select @newtblname
set @loadtbl = 'bulk insert '+@newtblname+' from '+char(39)+'\\wsana157\c$\tbl.txt'+char(39)+' with (FIRSTROW=2)'
--select @loadtbl
exec (@loadtbl) 
