Useful MSSQL Queries and Statements

Although I primarily use MySQL on linux servers, I’ve had a couple clients using MSSQL databases for all sorts of things from email marketing to order tracking. For these clients, I use Microsoft SQL Sever Management Studio Express and have saved many of the MSSQL queries I’ve had to create. Some of these will work fine in MySQL as is or with minimal tweaking.

Here’s a collection of the useful MSSQL Queries I’ve used and saved. This is by no means a comprehensive list of all the MSQL Queries and MSSQL statements, but I hope they can be useful for you.

MSSQL Variables

Being able to declare MSSQL Variables has saved so much time for sets queries that I need to run multiple times with slight variations.

This is a very basic example, but has everything you need. MSSQL Variables must begin with a “at symbol” (@). You first declare the MSSQL variables and assign it a character type (in this case, I’m just using varchar(55). 55 is the maximum number of characters to be stored in the variable. Then you set a value for the variables.

Declare @email1 varchar(55), @name1 varchar(55)
set @email1 = ‘%www.websitesinaflash.com%’
set @name1 = ‘%Ashton Sanders%’

SELECT *
FROMÂ Â email_database
WHERE email_address LIKE @email1
AND first_name LIKE @name1

MSSQL UNION

If you have two databases with emails in it, (which is a horrible practice btw) and you need to count how many email addresses you have without counting the same email twice, you can use the UNION call to make two separate SELECT statements on the two separate databases without returning the same email address twice:

SELECT DISTINCT email_address
ASÂ ‘Total Email Addresses”
FROMÂ email_database

UNION

SELECT DISTINCT email_address
FROMÂ second_email_database

MSSQL NOT IN

NOT IN can be a lifesaver. In this example, lets say you have a collection of email addresses that you send emails to. As a good email sender, you also have a database of all email addresses that return a hard bounce (5.x). Every time you send an email to your subscribers, you need to make sure you are not sending emails to these undeliverable or non-existent email addresses. Here’s an example of using the MSSQL NOT IN statment in a MSSQL Query:

SELECT DISTINCT email_address
AS ‘Email Subscribers without Bounced emails’
FROMÂ email_database
WHERE email_address NOT IN (

SELECT bad_email_address
FROM Email_Bounced_database
WHERE hard_bounce <> 0

)

MSSQL Email Domain Count

How many email addresses do you have for each domain name? Do you have more Hotmail subscribers than Yahoo subscribers? How many Mac.com subscribers do you have? Does it matter if you can’t get any emails delivered to a mac email server? All of these questions can be answered with this MSSQL statement. It will return how many “email_address”es you have for each domain:

SELECT distinct TOP 100
substring(email, charindex(‘@’, email), len(email)),
number = count(*)
FROM email_database
GROUP BY substring(email, charindex(‘@’, email), len(email))
ORDER BY number DESC

To quickly summarize what this SQL Query says:

Select the first 100 domains
Click out the domain name from the email address
Count the number of those domains are returned
Group the domains together
Organize by Count from most to least

MSSQL Count How Many Subscribers By Day

It’s always a good idea to have some statistical information stored for each email subscriber; things like ‘Date_Added’, ‘Source’, ‘Date_Removed’, etc. If you are storing something like Date_Added you can use this MSSQL Query to count how many subscribers you got each day during any given period of time:

SELECT
DATEADD(day, DATEDIFF(day, 0, date_added), 0) AS date_added,
COUNT(*) AS NumberofEmailSignUps
FROMÂ email_database
WHERE date_added
BETWEEN ‘2009-04-01 00:00:00’
AND ‘2009-05-01 00:00:00’
GROUP BY DATEADD(day, DATEDIFF(day, 0, date_added), 0)
Order By DATEADD(day, DATEDIFF(day, 0, date_added), 0)

To quickly summarize what this SQL Query says:

Select the Date ignoring the time.
Count the number of emails returned on each date
Only during the Month of April
Group the emails together by the date they signed up.
Organize by which date they signed up on.

REPLACE/REMOVE Characters from a MSSQL String

There are many kinds of website hacks out there, but one hack I had the pleasure of experiencing was a hack that added a string of characters to the end of certain fields in a MSSQL database. The string was never the same, but it always started with a “<” and ended with a “</script>”. This string was hoping to be parsed on the website (but never was), but it would make some pages not work at all. Here is a quick MSSQL Query that I used to remove the offending string of characters from the MSSQL Database.

SELECT field_name, replace(field_name, substring(pageurl,charindex(‘<‘, field_name) ,len(field_name)), ”)
from MSSQL_Database
where field_namelike ‘%</script>%’

INSERT CSV (Comma Seperated Value) File into MSSQL Database

This is a pretty sensitive function, but I have used it with success using the MSSQL BULK INSERT function. You need to make sure your CSV columns coincide with the database you are importing into. You will also see some variables you should change if you are using the bar “|” instead of commas to deliniate the different fields.

BULK INSERT database_name
FROM ‘c:\csvfile.csv’
–File must be saved on the server and includes all columns
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’,
DATAFILETYPE = ‘char’, — or ‘widechar’?
MAXERRORS = 100
)

INSERT Many Rows at Once into MSSQL Database

MySQL has a much easier way to do this, but if you are using a MSSQL this is probably the easiest way of inserting multiple rows into a database. Normally, you would have to do a new Insert function for each row you want to add:

INSERT INTO MSSQL_Database ([Name] ,[Email_Address])
VALUES (‘Name1′, ’email1′)

INSERT INTO MSSQL_Database ([Name] ,[Email_Address])
VALUES (‘Name2′, ’email2′)

INSERT INTO MSSQL_Database ([Name] ,[Email_Address])
VALUES (‘Name3′, ’email3′)

But you can use the UNION ALL function to join multiple rows into one happy INSERT

INSERT INTO MSSQL_Database ([Name] ,[Email_Address])

SELECT ‘Name1′, ’email1′
UNION ALL
SELECT ‘Name2′, ’email2′
UNION ALL
SELECT ‘Name3′, ’email3′

A word of warning, the way this works is it piles up all of the insert statements as one large MSSQL Query and then runs it on the database (as opposed to running each insert statement individulally). I ran into a problem when I tried doing this with 6000 rows of information and their wasn’t enough memory (probably on my local machine) to compile this huge query string before running it. I had to end up breaking down the Query into 12 different queries of 500 rows before I got it to work.

Enjoy,
Ashton Sanders