Bulk Insert from text file with variable columns
create table ##tbl (line varchar(1000))
bulk insert ##tbl
from '\minidragonc$HUD601_07-15-03.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)+'\wsana157c$tbl.txt'+char(39)+' with (FIRSTROW=2)'
--select @loadtbl
exec (@loadtbl)
7:20:03 AM 
|
