We can create a new copy of a table from an existing one, using the “select into” function.

However, while “select into” is a powerful function it can also be a server killer due to the i/o overhead copying a bazillion rows in a single batch (if the source was a large table), and more importantly, during the query, if you are creating a temp table (like #tablename) rather than a permanent table, it locks tempdb  (yes, the entire db) for the duration of the query, which means that no other temp tables can be created until the lock is removed (this is not a good thing). 

So if we use “select into” to create a temp or perm table, we want to do it really quickly.  Here’s the workaround for achieving the same goal but without breaking a sweat, OR, use it to simply create an empty copy of an existing table:

select   a.* into tmpAccount
from     dbo.Account a with (nolock)
where    1 = 2

That where clause is always false, which in turn says it can’t return any rows which eliminates all table i/o so the query runs licketysplit.  But does it build the table copy we were hoping for?  You bet. 

The above statement builds an exact replica of the source table but without any rows or i/o or locks or trauma, and releases the lock on tempdb almost instantly if you were building a temp table.

Then, we can leisurely fill the table in a manner that suits the row count.  If under 5000 rows, just do the insert.

If over, break it into 5000 row chunks using a “while” loop.  That prevents the transaction log from bursting when you try to cram 1.5 billion records into the new table…