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:
<!--- in english --->
=CONCATENATE("single quote", source cell, "single quote comma")
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.
from aTable
where something in
(
'1',
'2',
'3'
)
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?
Oh, and it doesn't fail gracefully where it feeds only data found in the first 1k. No, it just crashes the statement.
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.