Microsoft SQL Server forum - Create table from Text

Author

Create table from Text

Karen Sullivan

2005-05-25, 3:23 am

Hi, all. I'm fairly new to SQL, and I have been trying to create a table
from a text file. I have been looking at this for days, and can't find the
problem. I get a syntax error " Line 55: Incorrect syntax near
'DateUpdated'." Here is the query. Any suggestions would be appreciated,
as I am trying to learn and improve.

Use ACH
go

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ImportFiles]') and OBJECTPROPERTY(id, N'IsProcedure') =
1)
drop procedure [dbo].[ImportFiles]
GO

CREATE Procedure ImportFiles
@FilePath varchar(1000),
@MergeProc varchar(128) = 'MergeData'
AS
DECLARE @cmd varchar(2000),
@Command_String varchar(3000)

DECLARE @FileName varchar(1000),
@File varchar(1000)

CREATE table ##Import (datarow varchar(200))
CREATE table #Dir (datarow varchar(200))

DROP TABLE ACHParticipants

select @cmd = 'dir /B' + @FilePath
delete #Dir
insert #Dir exec master..xp_cmdshell @cmd

delete #Dir where datarow is null or datarow like '%not found%'

while exists (select * from #Dir)

BEGIN
select @FileName = min(datarow) from #Dir
select @file= @FilePath + @FileName
select @cmd = 'bulk insert'
select @cmd = @cmd + ' ##Import'
select @cmd = @cmd + ' from'
select @cmd = @cmd + ' @File,'
select @cmd = @cmd + ' with (FIELDTERMINATOR=''\
n'''
select @cmd = @cmd + ',ROWTERMINATOR = '':\n'')'

truncate table ##Import

-- import the data
exec (@cmd)

-- remove filename just imported
delete #Dir where datarow = @FileName

exec @MergeProc
END

drop table ##Import
drop table #Dir
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[MergeData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MergeData]
GO

CREATE PROCEDURE MergeData
AS
CREATE table ACHParticipants
(RoutingNum varchar(9),
OfficeCode varchar(1),
ServicingFRBNum varchar(9),
RecordType varchar(1),
ChangeDate varchar(8),
NewRoutingNum varchar(9),
BankName varchar(36),
BankAddress varchar(36),
City varchar(20),
State varchar(2),
Zipcode varchar(10),
Phone varchar(14),
StatusCode varchar(1),
DataView varchar(1),
Filler varchar(5),
DateUpdated datetime)

INSERT INTO ACHParticipants
(Routing_Number
, Office_Code
, Servicing_FRB_Number

, Record_Type_Code
, Change_Date
, New_Routing_Number
, Customer_Name
, Address
, City
, State_Code
, Zipcode
, Telephone
, Institution_Status_C
ode
, Data_View_Code
, Filler
, DateUpdated)

SELECT Substring(DataRow,1,
9) AS RoutingNum,
Substring(DataRow,10
,1) AS OfficeCode,
Substring(DataRow,11
,9) AS ServicingFRBNum,
Substring(DataRow,20
,1) AS RecordType,
convert(datetime,Sub
string(DataRow,21,6)
) AS ChangeDate,
Substring(DataRow,27
,9) AS NewRoutingNum,
Substring(DataRow,36
,36) AS BankName,
Substring(DataRow,72
,36) AS BankAddress,
Substring(DataRow,10
8,20) AS City,
Substring(DataRow,12
8,2) AS State,
Substring(DataRow,13
0,5) + '-' + Substring(DataRow,13
5,4) AS Zipcode,
Substring(DataRow,13
9,3) + '-' + Substring(DataRow,14
2,3) + '-' +
Substring(DataRow,14
5,4) AS Phone,
Substring(DataRow,14
9,1) AS StatusCode,
Substring(DataRow,15
0,1) AS DataView,
Substring(DataRow,15
1,5) AS Filler
DateUpdated datetime AS DateUpdated
FROM ##Import
GO

Thanks,
Karen

Simon Hayes

2005-05-25, 3:23 am

The error is probably because you are missing a comma after "AS
Filler". In general, you should avoid creating permanent tables from
within stored procedures, as it makes it very hard to control your data
model correctly, and if the proc is run multiple times you may have
problems.

A common approach is to create a permanent staging table (instead of
using a temporary one as you are), and bulk load your files into that.
A stored proc can then do the final INSERT into ACHParticipants, after
making any other data changes that might be needed.

You might also want to consider loading the data using bcp.exe instead
of BULK INSERT - it can often be easier to deal with file names etc.
outside the database, in a batch file or a script of some other sort.

Simon

Karen Sullivan

2005-05-25, 11:23 am

Thank you, Simon. I have put the comma in, and I am still getting the
error. I am going to try setting up a staging table - thanks again for the
suggestion.

Thank you for the good advice.
"Simon Hayes" <sql@hayes.ch> wrote in message
news:1117006217.229228.129070@g14g2000cwa.googlegroups.com...
> The error is probably because you are missing a comma after "AS
> Filler". In general, you should avoid creating permanent tables from
> within stored procedures, as it makes it very hard to control your data
> model correctly, and if the proc is run multiple times you may have
> problems.
>
> A common approach is to create a permanent staging table (instead of
> using a temporary one as you are), and bulk load your files into that.
> A stored proc can then do the final INSERT into ACHParticipants, after
> making any other data changes that might be needed.
>
> You might also want to consider loading the data using bcp.exe instead
> of BULK INSERT - it can often be easier to deal with file names etc.
> outside the database, in a batch file or a script of some other sort.
>
> Simon
>

Sponsored Links