Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2007
    Posts
    10

    Unanswered: SQL insert script

    Hi All,

    I am looking for a optimised SQL insert script, which inserts holiday date and holiday name into holiday table.
    at a time I would like to insert seven to eight rows.


    here is the talbe description

    SQL> desc HOLIDAY
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    HOLIDAY_DTE NOT NULL DATE
    HOLIDAY_NME VARCHAR2(100)


    I have one basic sql insert script, which contains seven to eight insert queries. but i don't think this optimised/generic one.

    Kindly let me know if there is any script which accept the input from a different file or so.

    Please send me your suggestions/thoughts asap.

    Thanks,
    KNS

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What DBMS are you using, I'm going to take a wild stab at Microsoft SQL Server 2005?
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Regards generic: you would normally have country in the holiday table unless your system will only work in one country - so it's not very generic at the moment. You could also have a flag to indicate that a holiday occurs on the same day each year but seeing as they all shift about depending on the phases of the moon etc you'd probably do better just adding in the holiday dates at the start of the year.

    Regards performance: how often are you wanting to insert new holiday values into your holiday table?

    Mike

  4. #4
    Join Date
    Feb 2007
    Posts
    10
    Hi,

    I am using Oracle database and there is no specific country in my table. Also I do this once in a while.

    Please suggest me.

    Thanks,

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Moved to Oracle topic.
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    What DBMS are you using, I'm going to take a wild stab at Microsoft SQL Server 2005?
    for future reference, VARCHAR2 == oracle

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by subbukns
    I have one basic sql insert script, which contains seven to eight insert queries. but i don't think this optimised/generic one.
    Why do you think the INSERT "is not optimised"?
    I can't think of any "non-optimised" INSERT statement (unless it uses an INSERT ... SELECT structure)
    But without seeing the statements that you suspect there is no way of telling whether they could be improved

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You might try INSERT ALL, if available:
    Code:
    SQL> create table holiday (hol_date date, hol_name varchar2(20));
    
    Table created.
    
    SQL> insert all
      2    into holiday values (sysdate, 'Today')
      3    into holiday values (to_date('30.09', 'dd.mm'), 'My birthday')
      4    into holiday values (trunc(sysdate, 'yyyy'), 'New Year')
      5    into holiday values (add_months(sysdate, -2), 'Two months ago')
      6 select * From dual;
    SQL> /
    
    4 rows created.
    
    SQL> select * from holiday order by hol_date;
    
    HOL_DATE HOL_NAME
    -------- --------------------
    01.01.07 New Year
    18.08.07 Two months ago
    30.09.07 My birthday
    18.10.07 Today
    
    SQL>

Posting Permissions

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