SQL无法从2个SQL查询导出JSON文件

发布于 2025-01-31 05:57:13 字数 850 浏览 3 评论 0原文

我有此存储过程，其中将一些数据从SQL Server表导出到JSON文件。

当我只有一个SQL查询时，它的工作正常，因为它仅导出一个JSON文件。

但是我要做的是从一个过程中从两个SQL Server表中创建两个JSON文件...

现在我的存储过程（工作）看起来像这样：

ALTER PROCEDURE [dbo].[SP_PopulateJsonFiles]
AS 
    DECLARE @sql varchar(1000)
    DECLARE @FileName varchar(500)

    SET @FileName = '\\C:\Projects\JsonFiles\Sqltable1.json'
    SET @sql = 'bcp "' + 
    'select * from Sqltable1'+
        --if I add this line down it breaks the procedure and exports nothing :( 
        'select * from [Audit]'+

    ' FOR JSON PATH, INCLUDE_NULL_VALUES" ' +
    'queryout  "'+@FileName+ '"' + 
    ' -c -S myMachine -d myDatabase-T'

    EXEC sys.XP_CMDSHELL @sql
GO

因此，现在仅在我只写'SELECT时才有效*从sqltable1'+中，但现在如果我在其中放置另一个选择语句。

我发现它与名称发生冲突，因为这种方式将尝试创建两个具有相同名称的文档，但是我该如何逃脱并创建两个带有不同名称的独立文件？

先感谢您。

原文

I have this stored procedure where I export some data from a SQL Server table to json file.

It works all fine when I only have one SQL query which makes sense because it exports only one json file.

But what I want to do is create two json files from two SQL Server tables from a single procedure...

Right now my stored procedure (working) looks like this:

ALTER PROCEDURE [dbo].[SP_PopulateJsonFiles]
AS 
    DECLARE @sql varchar(1000)
    DECLARE @FileName varchar(500)

    SET @FileName = '\\C:\Projects\JsonFiles\Sqltable1.json'
    SET @sql = 'bcp "' + 
    'select * from Sqltable1'+
        --if I add this line down it breaks the procedure and exports nothing :( 
        'select * from [Audit]'+

    ' FOR JSON PATH, INCLUDE_NULL_VALUES" ' +
    'queryout  "'+@FileName+ '"' + 
    ' -c -S myMachine -d myDatabase-T'

    EXEC sys.XP_CMDSHELL @sql
GO

So right now it only works if I only write 'select * from Sqltable1'+ but now if I put another select statement in it.

I get that it runs into a conflict with the name because this way will try to create two documents with the same name, but how can I escape this and create two separate files with different names maybe?

Thank you in advance.

分享到QQ

分享到微博

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

发布评论

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

走过海棠暮 2025-02-07 05:57:13

我找到了一个解决方案，因此我要发布，以防万一也可能需要它。

ALTER PROCEDURE [dbo].[SP_PopulateJsonFiles]
AS 
DECLARE @sql varchar(1000)
DECLARE @FileName varchar(500)

--for the first table and first file 
SET @FileName = '\\C:\Projects\JsonFiles\Sqltable1.json'
SET @sql = 'bcp "' + 
'select * from Sqltable1'+
' FOR JSON PATH, INCLUDE_NULL_VALUES" ' +
'queryout  "'+@FileName+ '"' + 
' -c -S myMachine -d myDatabase-T'
EXEC sys.XP_CMDSHELL @sql

--for the second table and second file
SET @FileName = '\\C:\Projects\JsonFiles\Sqltable2.json'
SET @sql = 'bcp "' + 
'select * from Sqltable2'+
' FOR JSON PATH, INCLUDE_NULL_VALUES" ' +
'queryout  "'+@FileName+ '"' + 
' -c -S myMachine -d myDatabase-T'
EXEC sys.XP_CMDSHELL @sql
GO

I found a solution to this so I am posting just in case someone might need this in the future too.

ALTER PROCEDURE [dbo].[SP_PopulateJsonFiles]
AS 
DECLARE @sql varchar(1000)
DECLARE @FileName varchar(500)

--for the first table and first file 
SET @FileName = '\\C:\Projects\JsonFiles\Sqltable1.json'
SET @sql = 'bcp "' + 
'select * from Sqltable1'+
' FOR JSON PATH, INCLUDE_NULL_VALUES" ' +
'queryout  "'+@FileName+ '"' + 
' -c -S myMachine -d myDatabase-T'
EXEC sys.XP_CMDSHELL @sql

--for the second table and second file
SET @FileName = '\\C:\Projects\JsonFiles\Sqltable2.json'
SET @sql = 'bcp "' + 
'select * from Sqltable2'+
' FOR JSON PATH, INCLUDE_NULL_VALUES" ' +
'queryout  "'+@FileName+ '"' + 
' -c -S myMachine -d myDatabase-T'
EXEC sys.XP_CMDSHELL @sql
GO

回复收藏 0 原文

~没有更多了~