How to use multiple tables in BCP utility copying from SQL Server to JSON file

Question

Hi All,

I want to copy 15 tables from my database and copy/load all the data into one JSON file. I am using BCP bulk utility to copy data, but I don't know how to write the script (select * from table1, select * from table2... using QUERYOUT or table1, table2,,,table n using OUT) for multiple tables.

Please let me know if this utility doesn't support my requirement then how can I implement this.

For Example:

SET @alenzi = 'bcp "SELECT (SELECT ID,City,State,PostalCode,Country, code,loadId' +
'FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER)' +
'FROM table1" ' +
'queryout "C:\My_File\tablesn.json" ' +
'-c -S servername -d dbname -T'
EXEC sys.XP_CMDSHELL @alenzi ;

Thanks All.

Answer 1

I want to copy 15 tables

A very theroretically (vague) question.
Do all tables have the same sturcture? Or are there relations between the tables? And why all into one file?
One way is to create a view which returns data from all tables; then use the view for BCP query.

Answer 2

Hi @hamb

but I don't know how to write the script (select from table1, select from table2... using QUERYOUT or table1, table2,,,table n using OUT) for multiple tables.

Refering to this doc: bcp Utility
" query " Is a Transact-SQL query that returns a result set. If the query returns multiple result sets, only the first result set is copied to the data file; subsequent result sets are ignored.
So you need to combine these 15 tables into a single dataset.
If those 15 tables are the same structure, then you could write a simple query using UNION to get one single dataset.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Try a query like this:

select   
    Table1 = json_query((select * from Table1 for json path, include_null_values) ),  
    Table2 = json_query((select * from Table2 for json path, include_null_values))  
   -- etc.  
for json path, without_array_wrapper  

It will generate a complex JSON that includes all of these tables. Check it with Management Studio, then try using BCP.

Specify the columns instead of '*'.

Answer 4

Viorel's question may take you there. But probably you have an idea of the JSON should look like. And no matter how you want the JSON to look, you probably can do it from T-SQL by spawning out to BCP. But just because you can, does not mean that you should. This task is probably a lot better done with a client-side program that reads the rows and then writes the JSON to file. No matter you use SSIS, Python. .NET, Java or whatever, it is likely to have better capabilities to control the JSON than T-SQL have.

And note that BCP is just another client-side program really. It is not the best to create JSON files though.