T-SQL 语句需要改进

发布于 2024-08-02 03:57:35 字数 758 浏览 6 评论 0原文

我有以下可以正确运行的 T-SQL 代码（在 SQL 2000 中）：

INSERT INTO M2MDATA01.dbo.cspopup (fcpopkey,
                                   fcpoptext,
                                   fcpopval,
                                   fnorder,
                                   fcpopacces)
   SELECT CSP69.fcpopkey,
          CSP69.fcpoptext,
          CSP69.fcpopval,
          CSP69.fnorder,
          CSP69.fcpopacces
     FROM M2MData69..CSPOPUP CSP69
    WHERE CSP69.fcpopkey = 'SHIPVIA'
          AND NOT EXISTS
                (SELECT CS01.identity_column
                   FROM m2mdata01..cspopup CS01
                  WHERE CS01.identity_column = CSP69.identity_column)

必须有一种更优雅的方法来实现它。我在这里在同一个脚本中以两种不同的方式引用同一个表。

有什么建议么？

原文

I have the following T-SQL code (in SQL 2000) which functions correctly:

INSERT INTO M2MDATA01.dbo.cspopup (fcpopkey,
                                   fcpoptext,
                                   fcpopval,
                                   fnorder,
                                   fcpopacces)
   SELECT CSP69.fcpopkey,
          CSP69.fcpoptext,
          CSP69.fcpopval,
          CSP69.fnorder,
          CSP69.fcpopacces
     FROM M2MData69..CSPOPUP CSP69
    WHERE CSP69.fcpopkey = 'SHIPVIA'
          AND NOT EXISTS
                (SELECT CS01.identity_column
                   FROM m2mdata01..cspopup CS01
                  WHERE CS01.identity_column = CSP69.identity_column)

There just has to be a more elegant way of doing it. I'm referencing the same table two different ways in the same script here.

Any suggestions?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

隱形的亼 2024-08-09 03:57:35

像这样？

INSERT INTO M2MDATA01.dbo.cspopup (fcpopkey,
                                   fcpoptext,
                                   fcpopval,
                                   fnorder,
                                   fcpopacces)
   SELECT CSP69.fcpopkey,
          CSP69.fcpoptext,
          CSP69.fcpopval,
          CSP69.fnorder,
          CSP69.fcpopacces
     FROM M2MData69..CSPOPUP CSP69
     LEFT
     JOIN m2mdata01..cspopup CS01
       ON CSP69.identity_column = CS01.identity_column
    WHERE CSP69.fcpopkey = 'SHIPVIA'
      AND CS01.indentity_column IS NULL

Like this?

INSERT INTO M2MDATA01.dbo.cspopup (fcpopkey,
                                   fcpoptext,
                                   fcpopval,
                                   fnorder,
                                   fcpopacces)
   SELECT CSP69.fcpopkey,
          CSP69.fcpoptext,
          CSP69.fcpopval,
          CSP69.fnorder,
          CSP69.fcpopacces
     FROM M2MData69..CSPOPUP CSP69
     LEFT
     JOIN m2mdata01..cspopup CS01
       ON CSP69.identity_column = CS01.identity_column
    WHERE CSP69.fcpopkey = 'SHIPVIA'
      AND CS01.indentity_column IS NULL

回复收藏 0 原文

伪心 2024-08-09 03:57:35

您可以将其作为左连接来执行，其中连接结果为空：

   SELECT CSP69.fcpopkey,
      CSP69.fcpoptext,
      CSP69.fcpopval,
      CSP69.fnorder,
      CSP69.fcpopacces
 FROM M2MData69..CSPOPUP CSP69
 LEFT JOIN m2mdata01..cspopup CS01 on SC01.indentity_column = CSP69.identity_column
WHERE CSP69.fcpopkey = 'SHIPVIA'
      AND CS01.identity_column is null

尽管根据我的经验，性能对最终结果的影响比代码的美观程度更大。您还可以执行以下操作：

   SELECT CSP69.fcpopkey,
      CSP69.fcpoptext,
      CSP69.fcpopval,
      CSP69.fnorder,
      CSP69.fcpopacces
 FROM M2MData69..CSPOPUP CSP69
WHERE CSP69.fcpopkey = 'SHIPVIA'
      AND CP69.identity_column not in
 (select identity_column from m2mdata01..cspopup)

You could do it as a left join where the join result is null:

   SELECT CSP69.fcpopkey,
      CSP69.fcpoptext,
      CSP69.fcpopval,
      CSP69.fnorder,
      CSP69.fcpopacces
 FROM M2MData69..CSPOPUP CSP69
 LEFT JOIN m2mdata01..cspopup CS01 on SC01.indentity_column = CSP69.identity_column
WHERE CSP69.fcpopkey = 'SHIPVIA'
      AND CS01.identity_column is null

Although in my experience performance will drive the end result more than the niceness of the code. You could also do a not in:

   SELECT CSP69.fcpopkey,
      CSP69.fcpoptext,
      CSP69.fcpopval,
      CSP69.fnorder,
      CSP69.fcpopacces
 FROM M2MData69..CSPOPUP CSP69
WHERE CSP69.fcpopkey = 'SHIPVIA'
      AND CP69.identity_column not in
 (select identity_column from m2mdata01..cspopup)

回复收藏 0 原文

~没有更多了~