Query Analyzer's Got My Back

Posted By : todd sharp Posted At : August 28, 2006 3:10 PM Posted In: SQL

5

So I had to find something to blog about today - well here it is. I was just working in Query Analyzer, trying to pull a quick dump of something to answer a data request. Often times I will get a list of data that I need to use as criteria for a query and I'll just get lazy and create a monstrous 'in list' in excel rather than pull the whole list into a temp table - which by the way is a quick timesaver.

For example, here is the data in Excel: To create your 'in list' just add a concatenate column: For clarity:
=CONCATENATE("'", A1, "',")
<!--- in english --->
=CONCATENATE("single quote", source cell, "single quote comma")
Which gives you: Now you just copy the new column paste into your in list and delete the final comma like so:
select stuff
from aTable
where something in
(
'1',
'2',
'3'
)
So anyhow, back to the point of this post. I had just finished one of these crafty queries when I decided to delete the query so I could work on another. I hit CTRL-A then Delete when I got this cool little message.

Comments (5)

Scott Thornton's Gravatar Hi,

Thanks for the tip. I've been manually typing the quotes and commas myself, and no doubt I will be using this tomorrow at work.

But what is the significance of the delete 1000 lines of code message box?

todd's Gravatar Not much really Scott - I just thought it was funny. I wonder why they choose '1000 lines' as the benchmark to warn you. I mean, if i'm deleting 900 lines of code accidentally and can't undo it, wouldn't I want that warning to come up then too?

Ryan's Gravatar As a side note, Oracle and I think MS SQL 2000 (and presumably 2005) limit you from running an in () statement in a where clause to 1000 items in the list. So, if you attempt to do a where x in (a,b,c,d....) and your in clause has >1000 items, your SQL will fail!

Oh, and it doesn't fail gracefully where it feeds only data found in the first 1k. No, it just crashes the statement.

todd's Gravatar Not sure about SQL2K Ryan. That is what I was using when I got the popup - the query ran fine and I had about 2500 items in the in list. However, if your statement were true - that would be where I would pull the data into a temp table and ran a subquery in the in list.

Rahul Narula's Gravatar I work around on this limitation of IN clause using
id IN ('1'....'1000') or id IN('1001'....,'2000') or ...... as many

Also regarding creating lists for queries, since I must admit I am pretty poor using excel I prefer using a regex in my favorite text editor, Editplus to do find replace for a regular expression like

find : ^([0-9]+)$ replace with : '\1',

after copying the column I need to quote from excel to editplus.