November 30, 2015 at 9:55 am
I have a bonus database that was created long ago and their are 3 separate tables with different bonus but all the same data. So what I want to do is create 2 tables, one with the bonus information that does not ever change and one with the payment information. This is an example of one table.
SELECT [intSRBBonusID]
,[intPersonnelID]
,[strControlNbr]
,[intBonusTypeID]
,[intTotalBonusAmt]
,[intPmtNbr]
,[intStatusID]
,[dtPmtDue]
,[fltPmtAmt]
,[strFileName]
,[strRemarks]
,[strTransactionNbr]
,[dtPaid]
,[strPmtRemarks]
,[intReceivedBy]
,[dtReceived]
,[bitValidation]
,[bitPrinted]
,[dtFinalized]
,[dtContract]
,[intRejectionReason]
,[strRejectionRemarks]
,[intUpdateBy]
,[dtUpdated]
FROM [tblSRB]
This information that is duplicative and I want to stick in table A
SELECT [intSRBBonusID]
,[intPersonnelID]
,[strControlNbr]
,[intBonusTypeID]
,[intTotalBonusAmt]
,[intStatusID]
,[fltPmtAmt]
,[intReceivedBy]
,[dtReceived]
,[bitValidation]
,[dtContract]
FROM [tblA]
The rest of the information I want to stick in table B because every bonus can have 3 to 5 payments.
SELECT [intpaymentId]
,[intSRBBonusID]
,[intPmtNbr]
,[intStatusID]
,[dtPmtDue]
,[fltPmtAmt]
,[strTransactionNbr]
,[dtPaid]
,[strPmtRemarks]
,[bitPrinted]
,[intRejectionReason]
,[strRejectionRemarks]
FROM [tblB]
SELECT [intSRBBonusID]
,[strFileName]
,[intReceivedBy]
,[dtReceived]
FROM [tblC]
the documents I want to put in table C along as right now they are a comma deliminated string and hard to manage and deal with. Same with the remarks as well as they want to track who made comment and when.
SELECT [intSRBBonusID]
,[strRemarks]
,[intReceivedBy]
,[dtReceived]
FROM [tblD]
So my question is how can I best break up each record to do what I want?? I thought about a cursor and loading all records into a temp table and then going through each one and inserting into table A grabbing the Identity and inserting into table B rest of info. Not sure if this is right way to do or is there a better way?? Thank you for assistance.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply