Converting lists for use in a SQL 'IN' statement

This website is a massive time saver http://delim.co/ 

Simply paste your list into the textbox, then select you delimiter (comma) then under converter options enter a single quote into open tag and close tag.

This will then produce a list of single quote enclosed comma separated values that you can drop straight into your SQL IN statement

Step 1
A1
B1
A2
B2
A3

B3

Step 2
'A1','B1','A2','B2','A3','B3'

Step 3
Select * from Product where ProductCode IN ('A1','B1','A2','B2','A3','B3')

How to set start value of auto increment in MySQL?

The auto_increment is known as the seed value in MSSQL, if you need to change the sequence of your numbers then you can do so by running the following SQL command

ALTER TABLE tablename AUTO_INCREMENT=9999;

Change the table name to your actual table name and the 9999 to be your actual start value.

Alternatively if you are using phpMyAdmin then select your table and from the top menu select operations, under table options edit the AUTO_INCREMENT field.

This worked in phpMyAdmin version 4.2.7.1

SpaceSniffer - another useful application

 Stop the presses! I can’t believe in all the time that this blog has been going that I haven’t once mentioned or credited an application that I've used countless times.

SpaceSniffer (http://www.uderzo.it/main_products/space_sniffer/) is freeware so what’s not to love? SpaceSniffer provides you with a visual representation of folders / file sizes on your hard drive. Today I ran out of disk space, I ran SpaceSniffer and managed to recover 30GB that was hiding in SQL Express (a database I had restored for testing and a transaction log file). It’s simple and intuitive to use.

MySQL migration to MSSQL

I use a mix of database servers depending on what application / project I am working on, normally it will be either SQL Server (inc Express) or MySQL.

I find working with SQL statements in SQL Server Management Studio to be much easier than creating statements on the fly, so its useful to have a copy of the MySQL databases on my MSSQL server, previously I have manually created a new database with the tables / structure that I need to create my SQL statements.

That all changed when I came across Intelligent Converters (http://www.convert-in.com/)  They have a great bit of software that will copy databases from just about anything to anything, in this case MySQL to MSSQL (http://www.convert-in.com/sql2mss.htm) The trial version of the software is limited to copying only 5 records per table, that's perfect if you just want the structure of the tables, for $49 you can have the full version of the software and for $99 you can get the MySQL Migration Toolkit which will convert any data source to or from MySQL.

SQL Backup And FTP

Since moving to SQL Server 2005/2008 (including SQL Express at home) I've been using SQL Server Management Studio to create a daily database backup. In my home environment my databases are very small (mostly development projects) so I place these backups into a folder than is configured to synchronize with the cloud. I'm not too worried about full server backups as the database server has minimal configuration done to it. In my business environments I have daily backups that backup the full system (including my SQL backup files). 

Despite using the Management Studio for nearly 9 years now I know very little about it, so perhaps it has the functionality built within it and I just don't process the knowledge (nor did my search results) on how to backup SQL databases to a network location, I also wasn't really in the mood to go fiddling with scripts etc.

I came across a great bit of software called SQL Backup And FTP (http://sqlbackupandftp.com/) 

The most basic (free) version of the software has a lot of functionality and setting up a job is easy.  

auto increment (auto_increment) in Excel with VBA (auto generate line numbers)

I was asked a question the other day, I'm still not quite sure why this was important to the user (see as Excel has row numbers provided automatically) and its also very easy to type the numbers and then drag down / fill to generate the next number, anyway the user had a large Excel document that they wanted to have automatic line numbers at various points (almost like a list or bullet points in Word)

I knocked up a quick macro that seems to do the job for the user

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

     

    If Target.Column > 1 And Target.Column <= 7 Then

   

        If Target.Row - 1 > 0 Then

            Cells(Target.Row, 1).Value = Target.Worksheet.Cells(Target.Row - 1, 1) + 1

        End If

      

    End If

End Sub

PHP IIS Got a whole lost easier

I can't remember if I’ve ever blogged about my problems setting PHP up on Windows under IIS, it can be a real headache... sometimes it works as per the documentation, other times (even on a clean server) it doesn't and I end up messing around with all kinds of settings and NTFS permissions, after several system reboots things finally start to work.

Recently I’ve been working on a project that involves using PHP and MSSQL (something I've not done before) it looks like php_mssql.dll is now history (up to PHP v5.2) the latest version of PHP uses a slightly different dll.

To my relief my latest PHP installation was a breeze, fire up the URL below, check all the stuff you want it to install and off it goes, all configured, all working (I wonder what the security is like)

http://www.microsoft.com/web/Downloads/platform.aspx

SQL - Update a table from another table

Just a quick blog mainly for my purposes, if you need to run a SQL update on a table and use another table for reference information e.g. updating prices for 2010 products e.t.c. then you can use the code below.

UPDATE destination_tbl

SET destination_column = (SELECT source_tbl.source_column

FROM source_tbl

WHERE source_tbl.criteria = destination_tbl.criteria)

WHERE EXISTS

(SELECT source_tbl.source_column

FROM source_tbl

WHERE source_tbl.criteria = destination_tbl.criteria)