CODEWITHSUNDEEP
sqlsql-server-2008ms-access
rugbert
rugbert

Reputation: 12633

Converting Access SQL Queries to SqlServer

I've been given the task of converting DOZENS of access tables, queries, macros, and reports to SQL Server and well its going horrible so far.

Like this for instance:

SELECT [Numbers by Quarter and Bedsize].SizeCategory, 
[Numbers by Quarter and Bedsize].Bedsize, 
[Numbers by Quarter and Bedsize].[SumOfNumber of CLABSI], 
[Numbers by Quarter and Bedsize].[SumOfCentral Line Days],
 Round(1000*[Numbers by Quarter and Bedsize].[SumOfNumber of CLABSI]/[Numbers by Quarter and Bedsize].[SumOfCentral Line Days],2) AS [State CLABSI Rate], 
 [Numbers by Quarter and Bedsize].SummaryYQ
FROM [Numbers by Quarter and Bedsize]
ORDER BY [Numbers by Quarter and Bedsize].SizeCategory, [Numbers by Quarter and Bedsize].SummaryYQ;

While I've seen SQL code before (mostly mysql) I've never seen anything like [Numbers by Quarter and Bedsize].SizeCategory

Are there any good tutorials to convert Access SQL to SQLServer? Please note, that I haven't actually touch either Access or SqlServer before in my life.

Upvotes: 2

Views: 3575

Answers (2)

onedaywhen
onedaywhen

Reputation: 57023

I've never seen anything like [Numbers by Quarter and Bedsize].SizeCategory

This is an example of a dot qualified name. The element to the left of the dot is the correlation name. If you do not provide an explicit correlation name in the from clause, a default correlation name equal to the table name is assumed. For example 

SELECT [Numbers by Quarter and Bedsize].SizeCategory
  FROM [Numbers by Quarter and Bedsize];

...is actually parsed as:

SELECT [Numbers by Quarter and Bedsize].SizeCategory
  FROM [Numbers by Quarter and Bedsize] AS [Numbers by Quarter and Bedsize];

You'll see correlation name referred to as an "alias", which is not correct but had sadly become the vernacular.

Arguably it makes the code easier to read is a short correlation name is chosen e.g. 

SELECT n.SizeCategory
  FROM [Numbers by Quarter and Bedsize] AS n;

The square brackets around the table name are quoted identifiers. In Standard SQL, the quoted identifier is the double quote ("). The Access database engine (ACE, Jet, whatever) is not compliant with the SQL Standard and uses square brackets. SQL Server can use both the Standard and the proprietary square brackets as quoted identifiers.

Access requires quoted identifiers when a data element would otherwise be illegal e.g. contains or begins with spaces or non-alpha characters. The Access query by example builder thing tends to add quoted identifiers around all data element names regardless of whether they are actually required.

The table name Numbers by Quarter and Bedsize requires quotes identifiers due to the presence of spaces. I suggest you take the port as an opportunity to rename data elements. While a complete revision is probably due (I would question the general quality of the names chosen), some 'low hanging fruit' would be the opportunity to replace space characters with underscores.

Following the above advice, and adding some formatting, the query would become:

SELECT n.SizeCategory, 
       n.Bedsize, 
       n.SumOfNumber_of_CLABSI, 
       n.SumOfCentral_Line_Days,
       Round(1000 * n.SumOfNumber_of_CLABSI / n.SumOfCentral_Line_Days, 2) 
          AS State_CLABSI_Rate, 
       n.SummaryYQ
  FROM Numbers_by_Quarter_and_Bedsize AS n
  ORDER 
     BY SizeCategory, SummaryYQ;

Obviously, the port will involve syntax changes but you also should also look for changes in behaviour. For example, division in Access SQL results in a FLOAT unless one of the values is of type DECIMAL in which case the result is a DECIMAL; the behaviour in SQL Server is different. One of the challenges you will face is the extremely poor documentation for Access e.g. the assertion I just made about division is not stated in the Access help nor any other official documentation from Microsoft; I had to figure it out for myself by trial and error.

I recommend you write comprehensive tests in both Access and SQL Server to ensure your ported SQL does not result in changed behaviour.

Upvotes: 3

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

What you are doing doesn't seem like the type of thing that would need to be done entirely by hand...

Have you considered using the the free Microsoft SQL Server Migration Assistant (SSMA) to "upsize" from Access to MS SQL Server 2008?

Upvotes: 1

Related Questions