I have an INSERT INTO SELECT query which appends about 500,000 rows of data to a table. The query takes about 22 minutes to run. I would like it to run in less than 5 minutes, if possible.
One solution I thought of was to append the 500,000 rows of data to an array (instead of a table). And when the array was filled (with 500,000 rows of data), to do a one-time transfer to a table.
My questions are, I don’t know how to transfer an array to a table, and I even if I can, I don’t know if it will be any faster than using INSERT INTO?
Answer The absolutely fastest way to import data into a table is to use the BULK INSERT command. This command has many options which affect performance, so you will want to examine them closely.
Also, be sure the table you are importing into doesn’t have any indexes, as they will slow down the import process. If your table has indexes, then you may want to drop them before the import, then re-add them after the import.
For some additional speed, be sure the file you are importing is on the same physical server, but a different physical drive, as the database you are importing into. This reduces network latency, and by placing it on a drive other than the drive that is holding your database, I/O performance problems will be minimized. SQL Server doesn’t support conventional arrays.]]>