Updating Tables from a Text File

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Updating Tables from a Text File

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-07 : 09:10:00

Scott writes "Can someone please take a look at the sp for me. When I run it I get unexpected end of file errors. I am trying to use a text file to update a table. The final result should allow members to upload a text file and press a button that will update the database. Maybe there is an easier way? Any help is greatly appreciated.

Thanks!

Scott

OS:
Windows 2000 Server
SQL Server 2000

Stored Procedure:

CREATE PROCEDURE ps_Update_Local99
@PathFileName varchar(100),
@OrderID integer,
@FileType tinyint
AS
DECLARE @SQL varchar(2000)
IF @FileType = 1
 BEGIN
  SET @SQL = "BULK INSERT TmpL99s FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '"",""') "
 END
ELSE
 BEGIN
 SET @SQL = "BULK INSERT TmpL99s FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = ',') "
 END
EXEC (@SQL)
INSERT tblAList (empno,lname,fname,address,city,zip,phone,status,email,na)
SELECT  CASE WHEN @FileType = 1 THEN SUBSTRING(empno,2,DATALENGTH(empno)-1)
            ELSE empno
        END,
        SUBSTRING(LName,1,DATALENGTH(LName)-0),
        SUBSTRING(FName,1,DATALENGTH(FName)-0),
        SUBSTRING(address,1,DATALENGTH(address)-0),
        SUBSTRING(city,1,DATALENGTH(city)-0),
        SUBSTRING(zip,1,DATALENGTH(zip)-0),
        SUBSTRING(phone,1,DATALENGTH(phone)-0),
        SUBSTRING(status,1,DATALENGTH(status)-0),
        SUBSTRING(email,1,DATALENGTH(email)-0),
            
        
        CASE WHEN @FileType = 1 THEN SUBSTRING(na,1,DATALENGTH(na)-1)
             ELSE na
        END
     
FROM tmpL99s

Update LOCAL99S 
SET LOCAL99S.status = tblAList.status,
LOCAL99S.City = tblAList.city,
LOCAL99S.zip = tblAList.zip,
LOCAL99S.address = tblAList.address,
LOCAL99S.email_name = tblAList.email
FROM tblAList	
WHERE LOCAL99S.Empno = tblAList.empno

TRUNCATE TABLE tmpL99s
TRUNCATE TABLE tblAList
GO"

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2002-03-07 : 10:01:22

Sounds like some rows in the text field don't have the expected number of delimiters or there's a ^Z misplaced. Is this text file created by BCP?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-07 : 10:42:27

try setting batchsize to 1 and errors to 0 then the bcp will stop when it reaches a line in error.
If you still have problems try it with a 1 line file with two fields to make sure the concept is ok.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources