Results 1 to 3 of 3

Thread: import data

  1. #1
    Join Date
    Mar 2004
    Posts
    660

    Unanswered: import data

    We have a database in sql server. Now in the front end, we have two fields called “latefee and latefeeDateReceived”. When next time the people get another late fee. They just replace the old one and enter the new one. But now we want to keep all the history. Keep whatever they get the late fee. I am thing maybe need to create another table called “latefee” to contain these value. Is is good idea? As later they may want to get a report for all the late fee in one company. The thing is, I need to retrieve the current latefee and latefeeDateReceived and import to “latefee” table. But I need to create primary key for the latefee table. How can I import? Only one thing is I can import the data first, then add primary key. If I import later, how can I import. (Explain, the data retrieved have fields called “CID, Latefee, latefeeDataReceived”. But the table “latefee” has four fields called, lateID, CID, Latefee, latefeeDateReceived. When I import, it is always failed. Anybody can give me an idea? Many thanks.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by yyu
    We have a database in sql server. Now in the front end, we have two fields called “latefee and latefeeDateReceived”. When next time the people get another late fee. They just replace the old one and enter the new one. But now we want to keep all the history. Keep whatever they get the late fee. I am thing maybe need to create another table called “latefee” to contain these value. Is is good idea? As later they may want to get a report for all the late fee in one company. The thing is, I need to retrieve the current latefee and latefeeDateReceived and import to “latefee” table. But I need to create primary key for the latefee table. How can I import? Only one thing is I can import the data first, then add primary key. If I import later, how can I import. (Explain, the data retrieved have fields called “CID, Latefee, latefeeDataReceived”. But the table “latefee” has four fields called, lateID, CID, Latefee, latefeeDateReceived. When I import, it is always failed. Anybody can give me an idea? Many thanks.
    I think you want to create a trigger (or maybe a couple of triggers). I think you would wind up with something like:

    CREATE Table DDL:
    Code:
    CREATE TABLE LateFee (
      LateFeeID Int Identity (1,1) NOT NULL,
      LateFee Decimal(10,2) NULL,
      LateFeeReceived datetime NULL
    )
    
    CREATE TABLE LateFeeLog (
      LateFeeLogID Int Identity (1,1) NOT NULL,
      LateFeeID Int NOT NULL,
      LateFee Decimal(10,2) NULL,
      LateFeeReceived datetime NULL
    )
    Then you would need to create a couple of triggers:
    Code:
    -- First trigger for inserts
    CREATE TRIGGER trgLateFeeInsert
    ON LateFee
    FOR INSERT 
    
    AS
    
    INSERT INTO LateFeeLog (LateFeeID, LateFee)
    SELECT LateFeeID, LateFee FROM inserted
    
    -- second trigger for updates
    CREATE TRIGGER trgLateFeeUpdate
    ON LateFee
    FOR UPDATE
    
    AS UPDATE dbo.LateFeeLog
    SET LateFeeReceived = inserted.LateFeeReceived
    FROM dbo.LateFeeLog inner join inserted ON
    	dbo.LateFeeLog.LateFeeID = inserted.LateFeeID
    Have you hugged your backup today?

  3. #3
    Join Date
    Mar 2004
    Posts
    660
    Thanks. This is big help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •