通过使用引用同一服务器上另一个数据库的子查询插入列

发布于 2024-11-24 15:42:02 字数 992 浏览 0 评论 0原文

我正在使用由一位知名博主编写的复制脚本的一部分。我想让下面列出的部分从一个仅包含 1 行的完全不同的表中添加 1 列。基本上，该单行表上有一个站点名称，我希望该表中的站点名称作为此 INSERT INTO 的一部分进行填充。

我知道 SQL 2005 引入了 OUTER APPLY，但我不确定这是否是最好的方法。欢迎任何建议。谢谢。

Insert Into dbo.dba_replicationMonitor
(
      monitorDate
    , publicationName
    , publicationDB
    , iteration
    , tracer_id
    , distributor_latency
    , subscriber
    , subscriber_db
    , subscriber_latency
    , overall_latency
    , SiteNameFromSiteInfoTable  --Need to add this


)
Select 
      @currentDateTime
    , @publicationToTest
    , @publicationDB
    , iteration
    , tracer_id
    , IsNull(distributor_latency, 0)
    , subscriber
    , subscriber_db
    , IsNull(subscriber_latency, 0)
    , IsNull(overall_latency, 
        IsNull(distributor_latency, 0) + IsNull(subscriber_latency, 0
        )
    , sitename = 'SELECT sitename FROM tblSiteInfo'  --need this query to insert as well
)
From @tokenResults;

我正在考虑一个变量，但我认为传递该变量还不够。非常感谢任何帮助。谢谢。

原文

I'm using parts of a replication script written by a well known blogger. I want to make the part I listed below add 1 more column from a totally different table that only holds 1 row. Basically that table with a single row has a site name on it, and I want that site name from that table to populate as part of this INSERT INTO.

I know SQL 2005 introduced OUTER APPLY, but I am not sure if that is the best method to go with. Any sugegstions are welcome. Thanks.

Insert Into dbo.dba_replicationMonitor
(
      monitorDate
    , publicationName
    , publicationDB
    , iteration
    , tracer_id
    , distributor_latency
    , subscriber
    , subscriber_db
    , subscriber_latency
    , overall_latency
    , SiteNameFromSiteInfoTable  --Need to add this


)
Select 
      @currentDateTime
    , @publicationToTest
    , @publicationDB
    , iteration
    , tracer_id
    , IsNull(distributor_latency, 0)
    , subscriber
    , subscriber_db
    , IsNull(subscriber_latency, 0)
    , IsNull(overall_latency, 
        IsNull(distributor_latency, 0) + IsNull(subscriber_latency, 0
        )
    , sitename = 'SELECT sitename FROM tblSiteInfo'  --need this query to insert as well
)
From @tokenResults;

I was thinking of a variable but I don't thnk passing the variable will be enough. Any help is greatly appreciated. Thanks.

分享到QQ

分享到微博

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

发布评论

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

妄断弥空 2024-12-01 15:42:02

您可以像平常一样加入第二个表。如果另一个表中只有一行（并且永远只有一行），它不会使您的结果加倍。所以，像这样：

 INSERT INTO dbo.dba_replicationMonitor (_column_list_)
 SELECT _@ToeknResultsColumns_, b.sitename
 FROM @TokenResults as a
 JOIN tblSiteInfo as b
 ON 1 = 1

You can just join to the second table as normal. If there's only one row in this other table (and will only ever be one row), it's not going to double your results. So, like this:

 INSERT INTO dbo.dba_replicationMonitor (_column_list_)
 SELECT _@ToeknResultsColumns_, b.sitename
 FROM @TokenResults as a
 JOIN tblSiteInfo as b
 ON 1 = 1

回复收藏 0 原文

~没有更多了~