用bcp嵌套INNER JOIN [英] Nested INNER JOIN with bcp

问题描述

因此，此查询在没有嵌套的SELECT/INNER JOIN的情况下可以正常工作. 不确定嵌套SELECT有什么问题，有什么想法吗?

So this query works fine without the nested SELECT/INNER JOIN. Not sure what is wrong with the nested SELECT, any ideas?

因此似乎无法使其与bcp一起使用

So can't seem to get it to work in conjunction with bcp

SELECT @SQLCmd = + 'bcp ' + 
                         '"SELECT ''<?xml version=""1.0"" encoding=""UTF-8""?>'' + ' + 

                         ' (SELECT CardId, Initials, firstname, lastname ' +
                         '      (SELECT CardId, SetVal ' +
                         '      FROM Business_data as bd ' +
                         '      INNER JOIN Business_set as bs on bd.SetVal=bs.id ' +
                         '      WHERE bd.CardID=ic.CardID ' +
                         '      FOR XML PATH(''BD''), TYPE ' +
                         '      ) ' +
                         ' FROM IndexCards as ic' + 
                         ' FOR XML PATH(''Employee''), ELEMENTS,  ROOT(''Employees'')) "' +
                         ' queryout '  +
                   @FileName +
                   ' -w -T -S' + @@SERVERNAME

这些是我正在使用的表

Indexcards

CardId  | Initials  | firstname | lastname  |
1       | AH        | Ash       | Hart      |
2       | AL        | Alex      | Lang      |

Business_set

ID  | Val     |
1   | Media   |
2   | Tech    |

Business_data

CardId  | SetVal  | 
1       | 1       |
2       | 1       |
2       | 2       |

推荐答案

在我提出的最后一个问题中，该问题位于数据库 Employees.dbo 中.我认为，您应该完全限定表的所有名称-更好-在选择之前放置USE Employees;.

From your last question I take, that this is in database Employees.dbo. I think, that you should either fully qualify all your table's names of - better - place an USE Employees; before your select.

我认为Dani Mathew是正确的，缺少逗号.子选择-从主选择看-仅是一个列，可以包含在输出中.

And I think, that Dani Mathew is right, that there is a comma missing. The sub-select is - seen form the main select - just a column to inlcude in the output.

尝试这样:

SELECT @SQLCmd = + 'bcp ' + 
                        '"USE Employees; SELECT ''<?xml version=""1.0"" encoding=""UTF-8""?>'' + ' + 

                        ' (SELECT CardId, Initials, firstname, lastname, ' +
                        '      (SELECT CardId, SetVal ' +
                        '      FROM dbo.Business_data as bd ' +
                        '      INNER JOIN dbo.Business_set as bs on bd.SetVal=bs.id ' +
                        '      WHERE bd.CardID=ic.CardID ' +
                        '      FOR XML PATH(''BD''), TYPE ' +
                        '      ) ' +
                        ' FROM dbo.IndexCards as ic' + 
                        ' FOR XML PATH(''Employee''), ELEMENTS,  ROOT(''Employees'')) "' +
                        ' queryout '  +
                @FileName +
                ' -w -T -S' + @@SERVERNAME

这篇关于用bcp嵌套INNER JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！