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…
Wow, incredible blog format! How long have you been running a blog for? you made blogging glance easy. The overall glance of your website is wonderful, as well as the content!
How do I start and use a blog? Also, what is the best blogging site?
excellant news! keep up the good work!!!
You have great info on this site… I think it’s super informative! Thank you!
This internet site is my intake , real superb pattern and perfect subject material .
I like the valuable information you provide in your articles. I’ll bookmark your weblog and check again here frequently. I am quite sure I will learn a lot of new stuff right here! Best of luck for the next!
It’s hard to come by experienced people on this subject, but you seem like you know what you’re talking about! Thanks
You really make it appear really easy with your presentation however I in finding this matter to be actually something that I feel I would by no means understand. It sort of feels too complicated and very extensive for me. I’m looking ahead for your next put up, I will try to get the grasp of it!
Hey I am so delighted I found your site, I really found you by accident, while I was looking on Aol for something else, Anyways I am here now and would just like to say thanks for a fantastic post and a all round exciting blog (I also love the theme/design), I don’t have time to read through it all at the minute but I have bookmarked it and also added your RSS feeds, so when I have time I will be back to read a great deal more, Please do keep up the great work.
Hi! I’m at work browsing your www from my new iphone 4! Just wanted to say I love reading your www and look forward to all your articles! Keep up the great work.
Hi! I’m at work browsing your www from my new iphone 4! Just wanted to say I love reading your www and look forward to all your articles! Keep up the great work.
nick website content material. I bookmarked it to my bookmark site list and will also be checking again soon.
Genuinely excellent, genuinely a terrific evaluate Pinterest even for non-affiliates. I am stumbling this a single for you… thanks!