INSERTING HEADER RECORD AND MOVING FILES

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

Import/Export (DTS) and Replication (2000)

INSERTING HEADER RECORD AND MOVING FILES

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-11-11 : 08:04:37

RANGA writes "Hi
Need some insight. Need fresh ideas.Wrote this procedure and would like to polish and optimise it for performance.

How do l bcp the data out to a specific location ie c:\DailyFilesHow do l insert the header record into exported textfile ???? Tried xp_cmdshell with echo had no luck
is this the best way to handle errors....? How do l insert failed records into an exception table
Move my files from local server to another machine. My move and del statements not working well. Sorry for the long proc

IF Object_Id('prcInsertNewMatters') Is Not Null
DROP PROC prInsertNewMatters
GO

CREATE PROCEDURE prcInsertNewMatters
AS
SET NOCOUNT ON

/*------------------------------------------------------------------------------------*
| Name: dbo.prcInsertNewMatters |
| Author: Raymond Mahlangu |
| Date: 09-11-2004 |
|-------------------------------------------------------------------------------------|
| Purpose: Insert CI Daily Newmatters |
| Input Parameters: None |
|-------------------------------------------------------------------------------------|
| Narration : This Procedure populates tbNewmatters with new matters on a daily basis|
| Writes Into tbLoadAudit the Table Name,Rows inserted & the Server Name |
| |
| Modifications: 09-11-2004 - Raymond Mahlangu (Perfromance Tuning) |
*-------------------------------------------------------------------------------------*/

DECLARE @IntErrorCode INT
SELECT @IntErrorCode = @@ERROR

DECLARE @ROWS INT
DECLARE @TotalBalance NUMERIC(13,2)

---Declare the variables to contain the BCP command
DECLARE @FileName VARCHAR(50),
@bcpCommand VARCHAR(2000)

DECLARE @Today VARCHAR(10)
SET @Today = REPLACE(CONVERT(CHAR(10),'2004-11-05',120 ),'-','/')

BEGIN TRANSACTION
IF @IntErrorCode = 0

BEGIN
TRUNCATE TABLE tbNewMatters

--insert new matters
INSERT INTO tbNewMatters
(
CustomerReference
,AccountReference
,OverDueBal
,TransactionDate
,Currency
,[Language]
,AttorneyCurrent
,ReasonCode
,TakeonPrevReasonCode
,InstalmetAmount
)
SELECT
DISTINCT

/** Check if the Ci client has an ABIL ClientNo & Populate the Field
Otherwise generate one from custcode
**/
CASE
WHEN c.IdentificationNumber IS NULL
THEN SUBSTRING(CONVERT(VARCHAR(10),a.CUSTCODE),3,5
)
ELSE c.CLIENTNUMBER
END
AS CustomerReference

,CONVERT(VARCHAR(15) , a.CustCode ) AS AccountReference
,CONVERT(NUMERIC(13,2) , a.Arrears ) AS OverDueBal

/** To get Date Format of YYYY/MM/DD **/
,REPLACE(
CONVERT(
CHAR(10),a.AccountOpenDate,120 ),'-','/'
) AS TransactionDate

,CONVERT(VARCHAR(3) ,'ZAR' ) AS Currency
,CONVERT(VARCHAR(3) ,'EN' ) AS Language

,LTRIM(LTRIM(CONVERT(CHAR(4),NULL))) AS AttorneyCurrent

/** Translate Flags to ABIL Flags **/
,CASE
WHEN a.AgencyNumber = 1 THEN CONVERT(NUMERIC(3,0),143
)
WHEN a.AgencyNumber = 2 THEN CONVERT(NUMERIC(3,0),850
)
WHEN a.AgencyNumber = 130
OR a.AgencyNumber = 3 THEN CONVERT(NUMERIC(3,0),400
)
ELSE CONVERT(NUMERIC(3,0),NULL
)
END AS ReasonCode

,CONVERT(NUMERIC(3,0) ,Null ) AS TakeonPre

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources