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
