|
|
Posted by Karen Sullivan on 05/25/05 07:40
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_Code
, 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,Substring(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,108,20) AS City,
Substring(DataRow,128,2) AS State,
Substring(DataRow,130,5) + '-' + Substring(DataRow,135,4) AS Zipcode,
Substring(DataRow,139,3) + '-' + Substring(DataRow,142,3) + '-' +
Substring(DataRow,145,4) AS Phone,
Substring(DataRow,149,1) AS StatusCode,
Substring(DataRow,150,1) AS DataView,
Substring(DataRow,151,5) AS Filler
DateUpdated datetime AS DateUpdated
FROM ##Import
GO
Thanks,
Karen
Navigation:
[Reply to this message]
|