SQL Server: INSERT new records INTO a table SELECT FROM multiple tables with WHERE conditions

We'll be working with three tables in this post.

Supposed you have:

1. [Credit] table
2. [Invoice] table
3. [Shipment] table 

[Credit] table

[Shipment] table

[Invoice] table

And now you want to add new record based on multiple tables with certain conditions applied. The barebone syntax is going to be something like this:

The query goes as such:

INSERT INTO [Credit] (InvoiceId, Description, Amount, Date, LastUpdateBy)

-- adding item into Credit table, columns: InvoiceId, Description, Amount, Date, and LastUpdateBy

SELECT [Invoice].InvoiceId, 'Discount Adjustment', ROUND(([Shipment].[Cost] * 0.10), 2), '2016-12-06', 8

-- from Invoice table with matching columns: [Invoice].InvoiceId, a constant text of 'Discount Adjustment', a simple

-- calculation: 10% discount of [Shipment].Cost, a constant date text of '2016-12-06', and a constant integer of 8

FROM [Shipment] INNER JOIN [Invoice] ON [Shipment].InvoiceId = [Invoice].InvoiceId

-- inner join the two tables: [Shipment] and [Invoice] with the matching value InvoiceId

WHERE [Invoice].CustomerId = 1205 AND [Invoice].Outstanding > 0.00 AND [Invoice].Credits = 0.00

-- and the where conditions: a specific CustomerId, Invoice Outstanding greater than 0, Credits equals to 0

This will query specific records from two tables: [Shipment] and [Invoice] based on the where conditions filter and then insert them into the [Credit] table.