INSERT inside WITH clause

Question

I basically learned SQL from Postgresql, I’m making use of SQL Server for the first time and I couldn’t figure out how to do it. Let’s say I have two tables. The first is a generic table of transactions (sales, leases, reservations, etc.)

CREATE TABLE [dbo].[Transactions]
(
    [id] BIGINT NOT NULL, 
    [client] INT NOT NULL, 
    [functionary] INT NOT NULL, 
    [dateTime] DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    [value] MONEY NOT NULL, 
    [typeTransaction] TINYINT NOT NULL, 
    CONSTRAINT [PK_Transactions] PRIMARY KEY ([id]), 
    CONSTRAINT [FK_Transactions_ToClients] FOREIGN KEY ([client]) REFERENCES [Clients]([person]), 
    CONSTRAINT [FK_Transactions_ToFunctionaries] FOREIGN KEY ([functionary]) REFERENCES [Functionaries]([person]), 
    CONSTRAINT [CK_Transactions_value] CHECK ([value] >= 0), 
    CONSTRAINT [CK_Transactions_typeTransaction] CHECK ([typeTransaction] > 0 AND [typeTransaction] < 5) 
)

The second table is a "daughter table" called [Sales]. That is, [Sales] "extends" the [Transaction] table by simulating an inheritance ([Sales] inherits from [Transactions]).

CREATE TABLE [dbo].[Sales]
(
    [transact] BIGINT NOT NULL, 
    [status] TINYINT NOT NULL DEFAULT 1, 
    CONSTRAINT [PK_Sales] PRIMARY KEY ([transact]), 
    CONSTRAINT [CK_Sales_status] CHECK ([status] > 0 AND [status] < 4), 
    CONSTRAINT [FK_Sales_ToTransactions] FOREIGN KEY ([transact]) REFERENCES [Transactions]([id]) 
)

I am trying to create a store to process [Sales] insertions, that is, SP should receive as a parameter the values of [Transactions]. You should then first insert these values into the [Transactions] table and then take the identifier and insert it into [Sales].

In Postgresql I solved this way:

WITH trans AS 
(
    INSERT INTO public."Transactions"
    (
        client, functionary, dateTime, value
    )
    VALUES
    (
        spp_client, spp_functionary,spp_dateTime,spp_value
    )
    RETURNING id
)
INSERT INTO public."Sales"( transact ) SELECT t.id FROM trans AS t;

I tried to do the same in SQL Server but am getting syntax errors from VS editor.

CREATE PROCEDURE [dbo].[insertSale]
    @spp_client AS int,
    @spp_functionary AS int,
    @spp_dateTime AS datetime,
    @spp_value AS money
AS
BEGIN

    WITH trans AS 
    (
        INSERT INTO [dbo].[Transactions]
        (
            [client], [functionary], [dateTime], [value]
        )
        VALUES
        (
            @spp_client, @spp_functionary,@spp_dateTime,@spp_value
        )
        OUTPUT INSERTED.id
    )
    INSERT INTO [dbo].[Sales]( [transaction] ) SELECT t.id FROM trans AS t;
END

I am getting syntax error in INSERT inside WITH and OUTPUT.

Answer 1

You have two ways to do this type of operation. One is by using Common Table Expression (CTE) as in your example, and the other is using a "temporary table".

I believe the error you reported is due to the use of OUTPUT. The correct syntax is not this.

---

Example 1, with CTE

WITH trans(id) AS 
(
    INSERT INTO [dbo].[Transactions]
    (
        [client], [functionary], [dateTime], [value]
    )
    OUTPUT INSERTED.id
    VALUES
    (
        @spp_client, @spp_functionary, @spp_dateTime, @spp_value
    )
)
INSERT INTO [dbo].[Sales]( [transact] ) SELECT t.id FROM trans AS t;

Example 2, with "temporary table"

    DECLARE @myTempID AS TABLE (id bigint);

    INSERT INTO [dbo].[Transactions]
    (
        [client], [functionary], [dateTime], [value]
    )
    OUTPUT INSERTED.id INTO @myTempID
    VALUES
    (
        @spp_client, @spp_functionary, @spp_dateTime, @spp_value
    );

    INSERT INTO [dbo].[Sales]( [transact] ) SELECT t.id FROM @myTempID AS t;

I cannot test at the moment. Try to run and return if something is wrong.