Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73

    Unanswered: need to user sysdate in bulk insert

    I need to use SYSDATE in a bulk insert is such a way that every row will get the current time and not the time that the operation has started.

    I've tried "insert into x(y) select sysdate from z" and it did not work.

    Any suggestions (except using triggers).

    Thanks,

    Tal Olier (otal@mercury.co.il)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: need to user sysdate in bulk insert

    Originally posted by tal_olier
    I need to use SYSDATE in a bulk insert is such a way that every row will get the current time and not the time that the operation has started.

    I've tried "insert into x(y) select sysdate from z" and it did not work.

    Any suggestions (except using triggers).

    Thanks,

    Tal Olier (otal@mercury.co.il)
    If you create a function like this:

    CREATE FUNCTION my_sysdate RETURN DATE
    IS
    RETURN SYSDATE;
    END;
    /

    Then do:

    insert into x(y) select my_sysdate from z;

    It does the trick. I don't know if the behaviour is guaranteed, but it certainly worked in my test just now.

  3. #3
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73
    it works, though on 1.5M records instead of 30 sec using sysdate, it took 5 min using my_sysdate

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by tal_olier
    it works, though on 1.5M records instead of 30 sec using sysdate, it took 5 min using my_sysdate
    Yes, that's the downside - it calculates the SYSDATE value 1.5M times, instead of just once! But if that's the logic you want, that's the price you will pay.

Posting Permissions

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