So let’s say you want to quickly populate a table with data. Until now, I’ve always done something like:
[loop from 1 to 1000] INSERT INTO TABLE (columnA, columnB, columnC) VALUES (columnA, columnB, columnC) [/loop]
This happens a lot when I’m updating cross-table relationships, for, say, group-membership. However, I’ve learned I can do this:
INSERT INTO TABLE (columnA, columnB, columnC) (SELECT columnA, columnB, columnC FROM TABLE_SOURCE WHERE ...)
Where, the ’…’ is the criteria to select from the source table. I’ve no idea if this is a best practice or not, but it sure is fast!
It changed an insert of 1700 rows from being about 2100 MS to being 21MS.