How do I use SQL Server to select into another table?

Question

I am consolidating a web service. I am replacing multiple calls to the service with one call that contains the data.

I have created a table:

CREATE TABLE InvResults 
(
    Invoices nvarchar(max),
    InvoiceDetails nvarchar(max),
    Products nvarchar(max)
);

I used (max) because I don't know how complex the json will get at this time.

I need to do some sort of selects like this (this is pseudocode, not actual SQL):

SELECT 
    (SELECT * 
     INTO InvResults for Column Invoices
     FROM MyInvoiceTable
     WHERE SomeColumns = 'someStuffvariable'
     FOR JSON PATH, ROOT('invoices')) AS invoices;

SELECT 
    (SELECT * 
     INTO InvResults for Column InvoiceDetails
     FROM MyInvoiceDetailsTable
     WHERE SomeColumns = 'someStuffvariable'
     FOR JSON PATH, ROOT('invoicedetails')) AS invoicedetails;

I don't know how to format this and my google skills are failing me at this point. I understand that I probably want to use an UPDATE statement, but I'm not sure how to do this in combination with the rest of my requirements. I'm exploring How do I UPDATE from a SELECT in SQL Server? but I am still at a halt.

The end result should be a table "InvResults" that has 3 columns containing one row with results from Select statements as JSON. The column names should be defined the same as the json root objects.

score 2 · Accepted Answer · answered Mar 30 '20 at 18:21

INSERT INTO InvResults(Invoices,InvoidesDetails)
SELECT 
    (SELECT * 
     INTO InvResults for Column Invoices
     FROM MyInvoiceTable
     WHERE SomeColumns = 'someStuffvariable'
     FOR JSON PATH, ROOT('invoices')) 
,
    (SELECT * 
     INTO InvResults for Column InvoiceDetails
     FROM MyInvoiceDetailsTable
     WHERE SomeColumns = 'someStuffvariable'
     FOR JSON PATH, ROOT('invoicedetails')) 
;

Because the SELECT.. FOR JSON is only returning 1 row above works.

The third field is easily to added, but left to do for yourself

How do I use SQL Server to select into another table?

1 Answers1