none
INSERT dentro de clausula WITH RRS feed

  • Pergunta

  • Eu basicamente aprendi SQL com PostgreSQL, estou pela primeira vez fazendo uso do SQL Server e não consegui descobrir como fazer isso nele.
    Digamos que eu tenha duas tabelas. A primeira é uma tabela genérica de transações (vendas, locações, reservas, 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) 
    )


    A segunda tabela é uma "tabela filha" chamada [Sales]. Ou seja, [Sales] "extende" a tabela [Transaction], simulando uma herança ([Sales] herda de [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]) 
    )

    Estou tentando criar uma store procedure para fazer inserções de [Sales], ou seja, a SP deve receber como parâmetro os valores de [Transactions]. Deve então primeiro inserir esses valores na tabela [Transactions] e depois pegar o identificador e fazer uma inserção em [Sales].

    Em PostgreSQL eu resolvia dessa forma:

    	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;

    Tentei fazer o mesmo em SQL Server mas estou recebendo erros de sintaxe do editor do VS.

    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

    Estou recebendo erro de sintaxe no INSERT dentro do WITH e no OUTPUT.

    terça-feira, 5 de julho de 2016 15:02

Respostas

Todas as Respostas

  • Deleted
    terça-feira, 5 de julho de 2016 15:44
  • Sua observação é válida, realmente eu havia esquecido de declarar o id como identity. Mas não é esse o fato que está ocasionando o erro de sintaxe no meu caso.
    Seu exemplo parece dar certo, porém fiquei curioso pelo motivo desse erro de sintaxe.
    terça-feira, 5 de julho de 2016 16:28
  • Deleted
    • Marcado como Resposta Matheus S. Silva terça-feira, 5 de julho de 2016 19:13
    • Não Marcado como Resposta Matheus S. Silva terça-feira, 5 de julho de 2016 19:13
    terça-feira, 5 de julho de 2016 17:12
  • Mesmo com suas modificações o VS ainda acusa erro de sintaxe

    terça-feira, 5 de julho de 2016 17:39
  • Matheus,

    Porque você esta criando um CTE para realizar o Insert?

    Não seria mais fácil utilizar diretamente o comando Insert?


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 5 de julho de 2016 17:56
  • Boa tarde,

    Matheus, acho que você poderia utilizar a função Scope_Identity() para obter o ID da linha inserida:

    https://msdn.microsoft.com/pt-br/library/ms190315(v=sql.110).aspx

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    terça-feira, 5 de julho de 2016 18:08
  • Matheus,

    Porque você esta criando um CTE para realizar o Insert?

    Não seria mais fácil utilizar diretamente o comando Insert?


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    N verdade eu já modifiquei o código seguindo a dica do José Diz:

    CREATE TYPE [dbo].[ArrayOfIdsType] AS TABLE
    (
    	Id INT
    );
    GO
    
    CREATE PROCEDURE [dbo].[insertSale]
    	@spp_client AS int,
    	@spp_functionary AS int,
    	@spp_dateTime AS datetime,
    	@spp_value AS money,
    	@spp_products AS [dbo].[ArrayOfIdsType] READONLY
    AS
    BEGIN
    
    	DECLARE @transid AS BIGINT;
    
    	BEGIN TRY
    		BEGIN TRANSACTION
    
    			INSERT INTO [dbo].[Transactions]
    			(
    				[client], [functionary], [dateTime], [value]
    			)
    			VALUES
    			(
    				@spp_client, @spp_functionary,@spp_dateTime,@spp_value
    			);
    
    			SET @transid = SCOPE_IDENTITY();
    
    			INSERT INTO [dbo].[Sales]( [transact] ) VALUES (@transid);
    
    			INSERT INTO [dbo].[TransactionsProducts] VALUES (@transid, (SELECT Id FROM @spp_products));
    
    		COMMIT TRANSACTION
    	END TRY
    
    	BEGIN CATCH
    		
    		ROLLBACK TRANSACTION
    		THROW
    
    	END CATCH
    END

    Mas ainda preciso testar essa procedure para saber se vai se comportar como eu espero. Em especial no ultimo insert, onde a ideia é inserir vários valores na tabela [dbo].[TransactionsProducts].

    CREATE TABLE [dbo].[TransactionsProducts]
    (
        [transact] BIGINT NOT NULL, 
        [product] INT NOT NULL, 
        CONSTRAINT [PK_TransactionsProducts] PRIMARY KEY ([transaction], [product]), 
        CONSTRAINT [FK_TransactionsProducts_ToTransactions] FOREIGN KEY ([transaction]) REFERENCES [Transactions]([id]), 
        CONSTRAINT [FK_TransactionsProducts_ToProduct] FOREIGN KEY ([product]) REFERENCES [Products]([id]) 
    )

    terça-feira, 5 de julho de 2016 19:25
  • Experimente deixar esse Insert da seguinte forma:

    			INSERT INTO [dbo].[TransactionsProducts]
    				SELECT @transid, Id FROM @spp_products;
    

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    terça-feira, 5 de julho de 2016 19:56
  • Deleted
    terça-feira, 5 de julho de 2016 22:36