MS Access查询转换为Sql Server [英] MS Access Queries Conversion to Sql Server

问题描述

我正在将许多访问查询转换为sql服务器存储过程.因此，sql需要满足t-sql标准.例如IIF等 有没有可以将大型访问查询转换为t-sql的工具?最好的方法是什么?

I am converting lots of access queries to sql server stored procedure. So the sql need to meet the t-sql standard. For example IIF etc Is there a tool that can convert big access queries to t-sql ? What is the best way of doing this ?

推荐答案

至于工具"只会为您转换查询，我还不知道. 此线程或

As far as a "tool" that will just convert the queries for you, I'm not aware of one. Neither is anyone on this thread or this site.

不过，我可以指导您几个地方，这可能有助于过渡.

There are a couple places I can direct you, though, that can possibly help with the transition.

如果您的查询使用任何[表格]！参考，也可能有一个问题. (我从未尝试过，但我将假定它不起作用.)

If your queries use any [Forms]! references, there could also be an issue with that. (I've never tried it, but I am going to assume it doesn't work.)

此参考资料可能对您可能需要在SQL Server中学习的东西提供了最详细的说明.从存储的查询到处理NULL，还有其他一些区别.与T-SQL相比，MS Access SQL也有差异.戈登·利诺夫(Gordon Linoff)在他的博客中简要介绍了10个重要区别.

This resource has probably the most detailed explanations on things you might need to learn in SQL Server. From stored queries, to handling NULLs to some of the other differences. There are also differences in MS Access SQL compared to T-SQL. Gordon Linoff briefly describes 10 important differences in his blog.

1. 访问不支持case语句，因此条件逻辑为 使用非标准的IIf()或Switch()函数完成.
2. 访问需要在每个成对的连接周围加上括号，从而导致 from子句中嵌套的扩散仅用于 混淆人们学习SQL.
3. 访问连接语法要求INNER for INNER JOIN.虽然可能 使用inner进行澄清是一个好主意，通常在 练习(在其他数据库中).
4. 访问权限不支持完全外部联接.
5. 访问权限不允许在子查询中进行联合或全部合并.
6. Access需要使用AS作为表别名.在大多数数据库中， 是可选的，我更喜欢仅将as用作列别名. 具有讽刺意味的是，Oracle中禁止将as用作表别名.
7. Access使用双引号分隔字符串(而不是单引号) (据我所知)是唯一使用&作为一个 字符串串联运算符.
8. Access对通配符使用*，而不是％.
9. 访问权限允许BETWEEN和.在其他数据库中允许这样做，但是 始终会得出false.
10. Access不支持窗口/分析功能(使用 和按条款划分).

1. Access does not support the case statement, so conditional logic is done with the non-standard IIf() or Switch() functions.
2. Access requires parentheses around each pair-wise join, resulting in a proliferation of nesting in from clauses that only serves to confuse people learning SQL.
3. Access join syntax requires the INNER for INNER JOIN. While it may be a good idea to use inner for clarify, it is often omitted in practice (in other databases).
4. Access does not support full outer join.
5. Access does not allow union or union all in subqueries.
6. Access requires the AS for table aliases. In most databases, this is optional, and I prefer to only use as for column aliases. Ironically, the use of as for table aliases is forbidden in Oracle.
7. Access uses double quotes to delimit strings (as opposed to single quotes) and is the only database (to my knowledge) that uses & as a string concatenation operator.
8. Access uses * for the wildcard in like rather than %.
9. Access allows BETWEEN AND . This is allowed in other databases, but will always evaluate to false.
10. Access does not support window/analytic functions (using the over and partition by clauses).

总而言之，不，我没有看到任何工具.

In sum, no, there is no tool that I have seen.

这篇关于MS Access查询转换为Sql Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！