T-SQL 旧式连接 *= 和 =*
我们有大约 150 个使用 *= 和 =* 类型 ANSI92? 的旧式查询和视图。加入。 有人知道可以帮助转换的工具/方法或脚本吗？或者我们是否必须艰难地完成所有 150 个工具/方法或脚本。
谢谢
Select PapersSent,
DateSent,
Code,
ActionDate,
ClientAction,
ClientContactRef,
PublishAppraisal,
PublishCV,
SponsorContactREF,
MeetingNotes,
InternalNotes,
Contact_AdminAction,
MeetingLocation
from tblMeetingNotes a,
tblPapersOptions b,
tblContactLog c
where a.CREF=@CREF and
a.CLID=@CLID AND
Isnull(PapersSent,0)*=Value AND
a.CREF*=c.CREF AND
a.CLID*=c.Contact_ID
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这可能不是您希望听到的，但这种类型的工具并不存在。在某些情况下，旧样式 JOIN 无法干净地转换为 SQL-92 样式，导致查询给出不同的结果，甚至需要重写查询。
即使有一个工具可以自动转换连接，您仍然需要测试每个查询以确保它按照您想要的方式进行转换，从而创建的工作量可能与手动完成的工作量一样多。
Erland Sommarskog 对于如何快速将旧式连接转换为 SQL-92 有一个很好的分步过程：http://www.sommarskog.se/Become-an-ANSI-star.doc
This probably isn't what you were hoping to hear, but this type of tool doesn't exist. There are situations where an old style JOIN won't cleanly convert to the SQL-92 style, causing the query to give different results, or even requiring the query to be re-written.
Even if there were a tool to automatically convert the joins, you would still need to test every query to make sure that it converted how you wanted it to, creating probably just as much work as it would have been to do it by hand.
Erland Sommarskog has a good step-by-step process on how you would quickly convert the old style joins to SQL-92: http://www.sommarskog.se/Become-an-ANSI-star.doc
在转换之前，我肯定会考虑设置某种测试框架，以便您可以比较结果。
如果所有这些都是视图，或者您可以将输出放入表中，这将是最简单的。
此时，您可以使用 EXCEPT 之类的东西来确保所有行都匹配。
过去，我使用存储过程编写生成表比较的代码，该存储过程获取表/视图并生成比较。甚至包括金额差异的数字/百分比阈值，其中一组存在尴尬的舍入问题 - 例如银行家舍入 (Teradata) 或基于 IEEE 浮点的舍入 (WebFocus)。
Before you convert, I would definitely see about setting up some kind of testing framework so you can compare the results.
This will be easiest if all these are views, or if you can get the output into tables.
At that point you can use things like EXCEPT to ensure that all rows match.
In the past, I've code generated table comparisons using stored procs which take the tables/views and generate the comparisons. Even including numeric/percentage thresholds for amount differences where one set has had awkward rounding problems - like banker's rounding (Teradata) or IEEE floating point-based rounding (WebFocus).
您可以编写数据库脚本并使用搜索和替换来更改其中的大部分内容并手动检查更困难的情况。正如 mfredrickson 指出的那样，请务必彻底测试所有查询，以防输出发生变化。
为了帮助搜索，尽管在编写数据库脚本时并非绝对必要，但请下载 Redgate 的 SQL 搜索（免费）可帮助您查找所有实例。即使您不使用它来完成此任务，拥有它也很方便。
You could script the database and use search and replace to change the bulk of them and manually inspect the more difficult cases. Be sure to test all the queries thoroughly in case the output has changed, as mfredrickson pointed out.
To help with the search, although not strictly necessary if you script the database, download Redgate's SQL Search (it's free) to help you find all the instances. Even if you don't use it for this task it's handy to have.