INSERT within clause WITH

Question

INSERT within clause WITH

Navigation

#1 by (0 votes)

1

I basically learned SQL with PostgreSQL, I am first making use of SQL Server and could not figure out how to do this in it. Let's say I have two tables. The first is a generic transaction table (sales, rentals, bookings, etc.)

CREATETABLE[dbo].[Transactions]([id]BIGINTNOTNULL,[client]INTNOTNULL,[functionary]INTNOTNULL,[dateTime]DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,[value]MONEYNOTNULL,[typeTransaction]TINYINTNOTNULL,CONSTRAINT[PK_Transactions]PRIMARYKEY([id]),CONSTRAINT[FK_Transactions_ToClients]FOREIGNKEY([client])REFERENCES[Clients]([person]),CONSTRAINT[FK_Transactions_ToFunctionaries]FOREIGNKEY([functionary])REFERENCES[Functionaries]([person]),CONSTRAINT[CK_Transactions_value]CHECK([value]>=0),CONSTRAINT[CK_Transactions_typeTransaction]CHECK([typeTransaction]>0AND[typeTransaction]<5))

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

CREATETABLE[dbo].[Sales]([transact]BIGINTNOTNULL,[status]TINYINTNOTNULLDEFAULT1,CONSTRAINT[PK_Sales]PRIMARYKEY([transact]),CONSTRAINT[CK_Sales_status]CHECK([status]>0AND[status]<4),CONSTRAINT[FK_Sales_ToTransactions]FOREIGNKEY([transact])REFERENCES[Transactions]([id]))

I'mtryingtocreateastoreproceduretomakeinsertionsof[Sales],thatis,theSPshouldbegiventheparametersof[Transactions].Youmustfirstenterthesevaluesinthe[Transactions]tableandthenpickuptheidentifierandinsertinto[Sales].

InPostgreSQLIsolveditthisway:

WITHtransAS(INSERTINTOpublic."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 I'm getting syntax errors from the 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'm getting INSERT syntax error inside WITH and OUTPUT.

sql sql-server stored-procedures

asked by anonymous 05.07.2016 / 17:51

1 answer

import methods from an unmanaged Bematech DLL c # Include associated class objects (ApplicationUser and UserProfile) - ApplicationUserManager and OWIN

score 0 · Answer 1

You have two ways to do this type of operation. One is using Common Table Expression 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 one.

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 can not test right now. Try to run and return if something goes wrong.