<cfsilence>

Query Analyzer's Got My Back

Posted At : August 28, 2006 4:10 PM | Posted By : todd sharp
Related Categories: SQL

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, "',")

=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) |

Print |

Send |

Code |

del.icio.us |

Linking Blogs | 2463 Views

Comments

[Add Comment]

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?

# Posted By Scott Thornton | 8/29/06 5:55 AM

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?

# Posted By todd | 8/29/06 6:44 AM

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.

# Posted By Ryan | 8/30/06 7:49 AM

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.

# Posted By todd | 8/30/06 8:15 AM

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.

# Posted By Rahul Narula | 12/17/06 11:49 AM

[Add Comment]