SQL Server - using the WITH clause in an INSERT statement

Description:

In this example we explain that how to use the WITH clause in an INSERT statement in SQL Server query. Or how to insert data using SELECT statement with WITH Clause in SQL Server. Or how to insert data with select statement with join two table using WITH clause and insert it into table in sql server. Or using the WITH clause in an INSERT statement in sql server. Or how to use CTE functionality with insert into select statement in SQL Server.

Here we demonstrate that SQL INSERT INTO SELECT statement using WITH clause.so below is the sql query that are used to insert record with select and WITH clause in sql server.

Example 1:

;WITH alias (y,z)

AS

(

    SELECT y,z FROM tableb

)

INSERT INTO tablea(a,b)

SELECT y, z

FROM alias

 Example 2:

;WITH PurchaseProduct

            AS

                        (SELECT [Purchase Order Id] AS [PO ID], [Product Id]  As [Product ID], SUM(ISNULL([Order Quantity],0)) As Qty,[IS available in MPR],

                        [IS available in SPAG] from [Purchase Products] GROUP BY [Purchase Order Id], [Product Id],[IS available in MPR],

                        [IS available in SPAG] )

            ,claim AS

                        (select [Purchase Order Id] AS [Claim PO ID], [Product Id] As [Claim Product ID], SUM(ISNULL([Quantity],0)) As ClaimQty from [Supplier Claim] group by [Purchase Order Id], [Product Id])

             Insert into [TEST_BORM_STAGING].dbo.[Non Matching]

              (

                        [Transaction Table Name],

                        [Transaction Table ID],

                        [Master Table Name],

                        [Matser Table Id],

                        [Error Type],

                        [IS available in MPR],

                        [IS available in SPAG]

               )

           

                        SELECT

                        'Purchase Products' as [Transaction Table Name],

                        P.[PO ID],

                        null as [Master Table Name],

                        null as [Master Table ID],

                        'Claim Quantity is Bigger than Purchase Quantity' as [Error Type],

                        P.[IS available in MPR],

                        p.[IS available in SPAG]

FROM PurchaseProduct AS P

LEFT OUTER JOIN claim AS C ON P.[PO ID] = C.[Claim PO ID] AND P.[Product ID] = C.[Claim Product ID]

WHERE C.ClaimQty > P.Qty