开发者

MS Access SQL to T-SQL Convertor [closed]

开发者 https://www.devze.com 2022-12-22 01:45 出处:网络
Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.
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. :)

  • 0

    精彩评论

    暂无评论...
    验证码 换一张
    取 消

    关注公众号