Results 1 to 5 of 5
  1. #1
    Join Date
    May 2008
    Posts
    2

    Unanswered: efficient ways of loading tables

    I'm trying to optimise the loading of some data into somes tables from external files, at present I use load table to load the data into a temporary staging table, I use an INSERT SELECT with ON EXISTING SKIP so as to only insert 'new' data into the table. I suspect there is not much scope for me to improve upon this, but if anyone knows otherwise I would really like some suggestions.

  2. #2
    Join Date
    May 2008
    Posts
    10

    RE:efficient ways of loading tables

    Hi cja,

    I would do following,

    1. bcp in the data into a temporary table, no need to be a hash(#) table.
    bcp command details can be found here

    2. then use insert command, to make sure only new entries are getting inserted.

    insert into DESTINATION_TABLE ( col_1, col_2, col_3...)
    select col_1, col_2, col_3...
    from TEMPORARY_TABLE A
    WHERE not exists
    ( select 1 from DESTINATION_TABLE B
    where A.col_1 = B.col_1
    and A.col_2 = B.col_2
    and A.col_3 = B.col_3 )

    Note : If you table size is considerably big, make sure you have proper indexes defined.

    Alternatively, if your file is having the all the latest data which you want to be available in your table, you can simply bcp in the complete data, but this may need extra caution as you are playing with existing data as well. But very much doable.

    cheers,

  3. #3
    Join Date
    May 2008
    Posts
    2
    Samir,

    I'm a bit of a newbie to Sybase, but I'm actually using SQL Anywhere, I didn't think bcp is available with SQL Anywhere ?.

    Chris

  4. #4
    Join Date
    May 2008
    Posts
    10
    indeed ... Anywhere doesn't seem to be supporting this.

  5. #5
    Join Date
    Mar 2008
    Posts
    96
    bcp is an OS level command so u can't execute from SQL Anywhere. But there is an option execute the following in SQL Anywhere
    xp_cmdshell "<os level command>"

    xp_cmdshell will work only if ur xpserver is up.

    and before executing that bcp from xp_cmdshell try to execute some other OS commands as pwd or ls -l etc. to check whether xp_cmdshell is working fine or not.

    hope this may help u.

Posting Permissions

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