This is going to be pretty obvious to anyone who uses SQL Server on a regular basis but I am not one of those people. In almost every other SQL editor that I use you can place a semi colon after your statement and hit cntrl+enter to execute the query. In SQL Server I always end up trying that only to add a new line to my statement. Now you could hit the execute button but I really dislike the mouse.

Anyways, this whole post was to remind myself that there is a keyboard shortcut for this and to be precise there are 3 shortcuts for this. You can use cntrl+e, alt+x or F5. This will execute the selected query or if nothing is selected it will run the entire query window.

This entry was posted on October 1, 2011 at 12:51 PM and has received 1448 views. Comments 0 | Print this entry.

I know this is probably old news to most but I was helping a friend out yesterday who didn't know this little trick so I thought I would share it. While some of you may have access to production databases its pretty common that these servers are guarded by a DBA. If I get a huge spread sheet of data I can't really import the data I need to send the sql statements to the dba and the script is run against the production database.

In this example I just got a spread sheet of 5 users that need to be imported into our users table.



Now for 5 users this is not a big deal but what about 100 or even a 1000 users. There is actually an easy way to create your insert statements using excel. First we will mark our D column as SQL. Next place click on the cell D1. What we are going to do is write a sql statement that will grab data from the columns a,b and c. First we write our normal insert statement but for the values we can evaluate the data in a cell using the following formula. The & is just used for concatenating. ="Insert into Users (userid,first,last) VALUES ("& A2 & ","& B2 &", "& C2 &");"

Then we can use that same formula for every row in our sheet. Simply copy and then past that formula all the way down for as many records as you have and you will end up with something like this.

While this is great you should see the issue. The user id field is fine but we have no single quotes around our strings, but we can fix that using the concatenate function. ="Insert into Users (userid,first,last) VALUES ("& A2 & ","& CONCATENATE("'",B2,"'") &", "& CONCATENATE("'",C2,"'") &");" Now our insert statements look a lot better. Again, not the coolest thing in the world but it really helps out nicely in this spot.

This entry was posted on August 4, 2010 at 8:51 AM and has received 4034 views. Comments 7 | Print this entry.

I came across a little problem today that I would like to share with you. I have an application that keeps track of emails sent to customers. My list method will return a query of emails sent. At first I was ordering them by DateSent DESC so that I would be able to view the emails from the last 1 sent. I quickly realized that I have emails that are drafts that have not yet been sent. I would like these emails to show up before any of the sent emails, so how do you do it.

The ISNULL replaces NULL with the specified replacement value. I can use todays date because no emails will be sent from the future.

This entry was posted on April 10, 2007 at 6:04 PM and has received 3096 views. Comments 1 | Print this entry.

This entry was posted on March 1, 2007 at 4:31 PM and has received 7301 views. Comments 7 | Print this entry.

So I ran into a problem last night. I had a bunch of old data that I needed to add a quick search to. One of the fields was a due date. We wanted to be able to search by due date. The main problem here is that all of the dates in the database where datetime types with a timestamp, probably from a Now() function.So the following code would not work and rightfully so.

The code above was comparing 2006-07-25 17:08:41.147 to 07/25/2006. So even though the customer was supplying a date that existed it would never return the record. I finally came up with a quick solution but I was wondering If I could get some feedback from everyone as to what the best approach to this would be. I ended up using the sql convert function. I may write more about this later but here is my code. The convert function will allow us for comparison to temporarily convert a field to the type and expression of our choice.

This entry was posted on July 28, 2006 at 10:47 AM and has received 2501 views. Comments 6 | Print this entry.