# Most efficient way to insert rows into a temp table in a stored procedure

I am working in SQL Server 2008. I have a stored procedure with 5 queries inside of it that select rows from very large fact tables (on the order of millions of rows) into another table (call it table_B). (Actually, the 5 queries used to be lots of queries, but I compressed them down to 5 because each query was doing a LEFT JOIN from the very large fact tables against very small dimension tables, i.e., each very large table was being scanned lots of times.) I need this other table (table_B) to persist, i.e., every time the stored procedure is executed, rows just keep getting added to it. table_B can become very large itself.

Currently, for each of the 5 queries, I insert rows into it via the INSERT INTO table_B SELECT ... method. A colleague recommended to me to de-couple my inserts and selects to gain some performance. He recommended to first insert the rows into separate temp tables, export the temp tables as flat files, and then insert the flat files into my table_B via SSIS. That way, we can get the performance of SSIS for inserts.

What is the best way for inserting rows into these temp tables? (I can either pre-define them or create them on-the-fly. So, I'm open to using either INSERT INTO or SELECT INTO methods.) The number of rows returned in each SELECT query will be large, in general. So, I need it to be as fast as possible.

The tables are very simple, i.e., no indexes, constraints, or primary keys. (Those may get implemented in the future, but I am not designing for it.)

Also, is there a way to automate the end-to-end process? I don't want to have users first execute the stored procedure, query the temp tables, export them, and then run SSIS to import them into the final table_B.

• I would expect an SSIS bulk insert to require the same insertion overhead of an 'INSERT...SELECT but without the SELECT overhead when sourced from flat files. You'll still need to incur the SELECT` overhead to create the files so I'm not sure the proposed method would improve performance. Are you saying you have no indexes on any of the tables or just the proposed temp tables? – Dan Guzman Jul 18 '15 at 19:36
• Correct, I have no indexes on any of the tables. The stored proc is for running data validations on external data that we will eventually move into a data warehouse. I have to accept the trashiest data possible. (As a separate project, I will first be removing duplicate rows in a stage table and then running this stored proc against an audit table, such that I can put indexes on the audit tables.) – skyline01 Jul 18 '15 at 20:07
• I doubt if you would get better performance by first selecting into a temp table, then writing into a file, and then loading into the final table. Note that in this approach, you are hitting the disk i/o twice: once for writing to the file and then to write to the final table. The only approach that can potentially beat 'select into' is reading the selected data into in-memory temp table, and then writing from temp table to the final table. But again, I do not expect significant difference. I would be curious to know the result :) – Ranvijay Jul 18 '15 at 20:40
• The right indexes will make all the difference. You can index trashy data--you don't have to have keys & constraints. If you really need the indexes to not stay there, it's possible that creating an index, running your 5 queries, and deleting the index will still be faster than your current process. – SQLFox Jul 20 '15 at 12:30