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.
As long as you aren’t using MySQL it’s fantastic practice.
As long as you aren’t using MySQL it’s fantastic practice.