On Fri, Aug 06, 2010 at 04:30:49PM +0100, Paul Sanderson scratched on the wall:
> Newbie question
> 
> I have two tables and I want to copy (occasionally) a few thousand
> columns that have been added to table1 into table2, both tables have
> the same unique key but otherwise the columns are different.
> 
> table2 will be initially populated from table1 using
> 
> INSERT into table table2 SELECT refno from table1 WHERE flag > 0
> 
> I could drop table2 and then do the above but will then obviously lose
> any data that has been modified in table2. How can I just append the
> new rows from table1 into table2

  In addition to Igor's suggestion, you may also be able to do an LEFT
  OUTER JOIN, and look for NULLs in the right-hand side (assuming your
  unique keys are NOT NULL):

  INSERT INTO table2
        SELECT table1.refno, ... 
          FROM table1 LEFT OUTER JOIN table2
             ON table1.key = table2.key
          WHERE table2.key IS NULL;

  I have no idea which would be faster.  You'll need to try and see.

  You could also try to do a sub-query that does a compound EXCEPT,
  although I would guess that's slower for this case.  I don't actually
  know, however.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to