MS Access SQL to T-SQL Convertor [closed]

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

---

开发者_如何转开发

We don’t allow questions seeking recommendations for books, tools, software libraries, and more. You can edit the question so it can be answered with facts and citations.

Closed 5 years ago.

Improve this question

Is there a tool that will convert MS Access SQL to T-SQL?

---

For my queries, I would prefer to do them by hand rather than let a tool do the translation.

I do not know of a tool, but here is a decent cheatsheet

http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/Quick-Access-JET-SQL-to-T-SQL-Cheatsheet.aspx

---

Just completed my first Access to SQL database conversion. Here's the process I used:

MS SQL Server Migration Assistant for all of the table structure and data. Despite many naming convention messages, all data and structures transferred without issue.

I read many warnings online about using SSMA for queries, so I did not go that route, opting to manually copy/paste the syntax in. MS SQL Management Studio is fairly intelligent about converting syntax from Access to T-SQL, but I encountered some gotchas:

Restricted field names like Key, Status, ID should be encapsulated in [].

IIF statements become CASE if you're using a SQL Server version earlier than 2012.

Nz becomes ISNULL (as Raj More aptly noted in the linked article above).

As tempting as it is to consolidate queries, resist until you've verified your output.

But the number one tip I didn't find in any guides is: utilize Round() and truncate float values (to whatever precision is dictated by your data). Manipulation of numeric data can produce very strange results that Access typically masks...

Odd behavior in SQL Studio: Select * does not show the record in question, but if you query that record with a specific where clause, it appears via magic. Truncate the values, and then Select * pulls it in.
I broke my head over this for days before figuring out it was related to float values with too many digits beyond the decimal. Someone with more experience can explain why, because I haven't the foggiest. :)