{"id":343,"date":"2009-05-02T15:48:49","date_gmt":"2009-05-02T21:48:49","guid":{"rendered":"http:\/\/websitedesign.www.websitesinaflash.com\/?p=343"},"modified":"2009-05-02T15:48:49","modified_gmt":"2009-05-02T21:48:49","slug":"useful-mssql-queries-and-statements","status":"publish","type":"post","link":"https:\/\/www.websitesinaflash.com\/2009\/05\/useful-mssql-queries-and-statements\/","title":{"rendered":"Useful MSSQL Queries and Statements"},"content":{"rendered":"

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<\/a> 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.<\/p>\n

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.<\/p>\n

MSSQL Variables<\/h3>\n

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

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.<\/p>\n

Declare @email1<\/strong> varchar(55), @name1<\/strong> varchar(55)
\nset @email1<\/strong> = ‘%www.websitesinaflash.com%’
\nset @name1<\/strong> = ‘%Ashton Sanders%’<\/p>\n

SELECT *
\nFROM\u00c2 \u00c2 email_database
\nWHERE email_address LIKE @email1<\/strong>
\nAND first_name LIKE @name1<\/strong><\/p><\/blockquote>\n

MSSQL UNION<\/h3>\n


\nIf 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:<\/p>\n

SELECT DISTINCT email_address
\nAS\u00c2 ‘Total Email Addresses”<\/em>
\nFROM\u00c2 email_database<\/p>\n

UNION<\/strong><\/p>\n

SELECT DISTINCT email_address
\nFROM\u00c2 second_email_database<\/p><\/blockquote>\n

MSSQL NOT IN<\/h3>\n

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:<\/p>\n

SELECT DISTINCT email_address
\nAS ‘Email Subscribers without Bounced emails’<\/em>
\nFROM\u00c2 email_database
\nWHERE email_address\u00c2 NOT IN<\/strong> (<\/p>\n

SELECT bad_email_address
\nFROM Email_Bounced_database
\nWHERE hard_bounce <> 0<\/p>\n

)<\/p><\/blockquote>\n

MSSQL Email Domain Count<\/h3>\n

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:<\/p>\n

SELECT distinct TOP 100
\nsubstring(email, charindex(‘@’, email), len(email)),
\nnumber = count(*)
\nFROM email_database
\nGROUP BY substring(email, charindex(‘@’, email), len(email))
\nORDER BY number DESC<\/p><\/blockquote>\n

To quickly summarize what this SQL Query says:<\/p>\n

Select the first 100 domains
\nClick out the domain name from the email address
\nCount the number of those domains are returned
\nGroup the domains together
\nOrganize by Count from most to least<\/p><\/blockquote>\n

MSSQL Count How Many Subscribers By Day<\/h3>\n

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<\/em> you can use this MSSQL Query to count how many subscribers you got each day during any given period of time:<\/p>\n

SELECT
\nDATEADD(day, DATEDIFF(day, 0, date_added), 0) AS date_added,
\nCOUNT(*) AS NumberofEmailSignUps
\nFROM\u00c2 email_database
\nWHERE date_added
\nBETWEEN ‘2009-04-01 00:00:00’
\nAND ‘2009-05-01 00:00:00’
\nGROUP BY DATEADD(day, DATEDIFF(day, 0, date_added), 0)
\nOrder By\u00c2 DATEADD(day, DATEDIFF(day, 0, date_added), 0)<\/p><\/blockquote>\n

To quickly summarize what this SQL Query says:<\/p>\n

Select the Date ignoring the time.
\nCount the number of emails returned on each date
\nOnly during the Month of April
\nGroup the emails together by the date they signed up.
\nOrganize by which date they signed up on.<\/p><\/blockquote>\n

REPLACE\/REMOVE Characters from a MSSQL String<\/h3>\n

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.<\/p>\n

SELECT field_name, replace(field_name, substring(pageurl,charindex(‘<‘, field_name) ,len(field_name)), ”)<\/strong>
\nfrom MSSQL_Database
\nwhere field_namelike ‘%<\/script>%’<\/p><\/blockquote>\n

INSERT CSV (Comma Seperated Value) File into MSSQL Database<\/h3>\n

This is a pretty sensitive function, but I have used it with success using the MSSQL BULK INSERT<\/strong> 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.<\/p>\n

BULK INSERT <\/strong>database_name
\nFROM ‘c:\\csvfile.csv’
\n–File must be saved on the server and includes all columns
\nWITH
\n(
\nFIELDTERMINATOR = ‘,’,
\nROWTERMINATOR = ‘\\n’,
\nDATAFILETYPE = ‘char’,\u00c2 — or ‘widechar’?
\nMAXERRORS = 100
\n)<\/p><\/blockquote>\n

INSERT Many Rows at Once into MSSQL Database<\/h3>\n

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:<\/p>\n

INSERT INTO MSSQL_Database\u00c2 ([Name] ,[Email_Address])
\nVALUES (‘Name1′, ’email1′)<\/p>\n

INSERT INTO MSSQL_Database\u00c2 ([Name] ,[Email_Address])
\nVALUES (‘Name2′, ’email2′)<\/p>\n

INSERT INTO MSSQL_Database\u00c2 ([Name] ,[Email_Address])
\nVALUES (‘Name3′, ’email3′)<\/p><\/blockquote>\n

But you can use the UNION ALL <\/strong>function to join multiple rows into one happy INSERT<\/strong><\/p>\n

INSERT INTO MSSQL_Database\u00c2 ([Name] ,[Email_Address])<\/p>\n

SELECT ‘Name1′, ’email1′
\nUNION ALL<\/strong>
\nSELECT ‘Name2′, ’email2′
\nUNION ALL<\/strong>
\nSELECT ‘Name3′, ’email3′<\/p><\/blockquote>\n

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.<\/p>\n

Enjoy,
\nAshton Sanders<\/p>\n","protected":false},"excerpt":{"rendered":"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....","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[34,37],"tags":[114,243,244,245,246,247,248,250,366],"acf":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.websitesinaflash.com\/wp-json\/wp\/v2\/posts\/343"}],"collection":[{"href":"https:\/\/www.websitesinaflash.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.websitesinaflash.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.websitesinaflash.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.websitesinaflash.com\/wp-json\/wp\/v2\/comments?post=343"}],"version-history":[{"count":0,"href":"https:\/\/www.websitesinaflash.com\/wp-json\/wp\/v2\/posts\/343\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.websitesinaflash.com\/wp-json\/wp\/v2\/media?parent=343"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.websitesinaflash.com\/wp-json\/wp\/v2\/categories?post=343"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.websitesinaflash.com\/wp-json\/wp\/v2\/tags?post=343"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}